[Base]: Query give no results by reason of empty or null field

asked 2019-10-10 23:25:33 +0100

joppla
13 ●2 ●5 ●13

updated 2019-10-11 13:08:09 +0100

Hello,

Used db: mariadb; OS: Windows10 & Xubuntu18.04; LO: 6.3.x

I made a sql query with the following suntax:

SELECT CONCAT(`SurName`, ', ', `FirstName`) AS `FullName`, `MemberID` FROM `Members`

The result is a table with two columns: FullName and MemberID In de field Fullname I got the name with the syntax: Johnson, Boris or May, Theresa.

But if the field of the FirstName is empty the field with Fullname is empty. If I have f.e. a space in the field FirstName I will see f.e. Johnson,.

How can I make the SQL Query in this way that I don't need to fill in everywhere a space in a field?

edit retag flag offensive close merge delete

Comments

Hello,

Please, when dealing with Base, always mention database used as items such as SQL can vary with database. It is usually helpful to have OS and specific LO version (may have bug fixed is a different release).

Ratslinger ( 2019-10-11 01:18:32 +0100 )edit

db, os, lo version added.

joppla ( 2019-10-11 13:08:57 +0100 )edit

add a comment

Comments

Thanks! It works.

joppla ( 2019-10-11 13:01:20 +0100 )edit

If it is also possible that the surname is missing, you may need that COALESCE() construct also on the first part in CONCAT().

Current versions of MariaDB supports "concatenate with separator", CONCAT_WS(), which handles null values gracefully. First parameter is the separator of choice, in this case comma + space:

SELECT CONCAT_WS(', ', `SurName`, `FirstName`) AS `FullName`,`MemberID` FROM `Members`

This does not add leading/trailing comma, which means that it looks cleaner, but you can't distinguish between e.g. Jackson used as first name and as surname if the other name is missing.

keme ( 2019-10-11 13:36:24 +0100 )edit

CONTCAT_WS gives also nice possibilities! Thanks!

joppla ( 2019-10-11 13:50:19 +0100 )edit

add a comment

[Base]: Query give no results by reason of empty or null field

Comments

1 Answer

Comments

Question Tools

Stats

Related questions

[Base]: Query give no results by reason of empty or null field edit

Comments

1 Answer

Comments

Question Tools

Stats

Related questions

[Base]: Query give no results by reason of empty or null field