Frequently asked questions - Base

From The Document Foundation Wiki
< Faq‎ | Base
Jump to: navigation, search


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.