Frequently asked questions - Base

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

How do I use SQL to concatenate fields?

Consider a database with one table, Animateur, containing, in addition to a primary key field, a field Nom and a field Prenom.

The query to concatenate these two fields can be written in several ways. The following solutions have been tested on HSQL.

"Animateurs"."Prenom" || ' ' || "Animateurs"."Nom"
COALESCE("Animateurs"."Prenom" || ' ' || "Animateurs"."Nom")
CONCAT( CONCAT( "Animateurs"."Prenom", ' ' ), "Animateurs"."Nom" )
"Prenom" + SPACE( 1 ) + "Nom"
"Prenom" + CHAR( 32 ) + "Nom"

Notes: Of these solutions, the one using the function CONCAT is the only one preventing null values.

In the following example there is no first name saved in the row "Mozart". The result of concatenation is Null for each statement except those containing CONCAT. CONCAT prevents null fields being included