Frequently asked questions - Base
From The Document Foundation Wiki
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.
Use a query like the following :
SELECT "NOM" FROM "Table1" WHERE "NOM" <> TRIM (' ' FROM "NOM" )
- 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.
- Use a query like the following :
UPDATE "Table1" SET "NOM" = TRIM(BOTH ' ' FROM "NOM")
- 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.