[base SQL] concatenate with empty fields?

[MatthijsG]

The following SQL-code is working, but not for entry’s with empty fields. If the field ‘voorvoegsel’ (dutch for prefix last name) is empty, there’s no result. Other entry’s are shown. If i use only the title (aanhef) and first name (voornaam) every entry is shown.

How can i get results when one field is empty?

SELECT 
"contacten"."Aanhef" ||' '|| "contacten"."Voornaam" ||' '|| "contacten"."Voorvoegsel" ||' '|| "contacten"."Achternaam" 
AS 
"volledigenaam" 
FROM "contacten" 
ORDER BY "contacten"."Voornaam" ASC

[Ratslinger]

This will work by replacing the empty field with a value - in this case I used DATA MISSING. Hopefully I converted your statement correctly:

SELECT 
"Aanhef" ||' '|| "Voornaam" ||' '|| "Voorvoegsel2" ||' '|| "Achternaam" 
AS 
"volledigenaam" 
FROM (SELECT 
"contacten"."Aanhef", "contacten"."Voornaam", CASE WHEN "contacten"."Voorvoegsel" <> '' THEN 
"contacten"."Voorvoegsel" ELSE 'DATA MISSING' AS "Voorvoegsel2", "contacten"."Achternaam" 
FROM "contacten")
ORDER BY "contacten"."Voornaam" ASC

If I messed up the statement, here is the one tested with which works:

SELECT "NAME" || ' ' || "MYGROUP" || ' ' || "MYTYPE2" || ' ' || "MYVARIETY"
AS  "CONTACT" FROM (SELECT
NAME, MYGROUP, MYVARIETY, CASE WHEN MYTYPE <> '' THEN MYTYPE ELSE 'DATA MISSING' END AS MYTYPE2
FROM ANCIENTITEM) ORDER BY CONTACT ASC

Edit: A version which checks/replaces all NULL fields (shorter using COALESCE):

SELECT 
COALESCE("contacten"."Aanhef", 'Nee Aanhef') ||' '|| COALESCE("contacten"."Voornaam", 'Nee Voornaam') ||' '|| COALESCE("contacten"."Voorvoegsel", 'Nee Voorvoegsel') ||' '|| COALESCE("contacten"."Achternaam", 'Nee Achternaam')
AS  "volledigenaam" 
FROM "contacten" 
ORDER BY "contacten"."Voornaam" ASC

[MatthijsG]

For some strange reason i can’t get it to work. Must try your edit.
Tnx so far!

[Ratslinger]

@MatthijsG I hope you got this to work. If not, please specify which LO version, which OS and what DB (embedded, split, MySQL, etc.) you are using.

[peterwt]

An empty text field is stored as null which prevents concatenation from working. If the only field that may be empty is voorvoegsel then the solution provided by @Ratslinger is fine, but if other fields can be empty then the case statement will become very long.

An alternative is to set the default value for any of the fields that can be empty to space in the Table. These fields will then never be null and the SQL you are using will work.