Frequently asked questions - Base

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

• Wiki Home
• Development
• Design
• QA
• Events
• Documentation
• Website
• Localization
• Accessibility
• Marketing
• Diversity
• Wiki Help

• Documentation Home
• Official Publications
• Other Resources
• FAQ
• HowTo
• Macros
• Docs Development

• FAQ Home
• General
• Writer
• Calc
• Impress
• Draw
• Math
• Base

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.