Faq/Base/126

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.



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.