Faq/Base/126

    From The Document Foundation Wiki
    < Faq‎ | Base


    How can I suppress unwanted blank characters?

    Context: after importing from an external table, some fields are filled with unwanted spaces before and after the text.

    Querying data

    Use a query like the following :

    SELECT "NOM" FROM "Table1" WHERE "NOM" <> TRIM (' ' FROM "NOM" )


    Explanation

    • The function TRIM removes one or more characters at the beginning or end of the string. Specify whether leading, trailing, or both with the keywords LEADING, TRAILING, or BOTH (default).
    • Similarly there are functions LTRIM and RTRIM which specifically remove spaces from the left and right respectively of the expression given as the parameter.
    • These function can be used in both draft and SQL mode.
    • In the example given, no keyword specifying position is given therefore the default BOTH will be applied.


    Select 1- trailing spaces 2- leading spaces 3- both


    Changing data

    • Use a query like the following :


    UPDATE "Table1" SET "NOM" = TRIM(BOTH ' ' FROM "NOM")


    • Explanation
      • This command is executed via the Utils > SQL menu or by a macro.
      • It removes leading and trailing spaces from the field "Nom" in every row.