Base query - CONCAT function

users
#1

I wanted to use the CONCAT function in a Base query. However, it seems
that if ONE of the elements of the list to be concatenated is NULL then
the CONCAT function returns a NULL. Am I right? Is it a bug or a
feature?

Kind Regards

Harvey Nimmo

#2

Hey Harvey,

I wanted to use the CONCAT function in a Base query. However, it seems
that if ONE of the elements of the list to be concatenated is NULL then
the CONCAT function returns a NULL. Am I right? Is it a bug or a
feature?

feature, because anythin, which is connected with NULL should be NULL.

Do the following

SELECT "surname"||', '||"forename" AS "name" FROM "table"

for concatenate. You could concatenate more than two fields in this way.
If one field is NULL all will be NULL.

Now

SELECT "surname"||IFNULL(', '||"forename",'') AS "name" FROM "table"

will set '' for comma and forename if forename is NULL. Will only the
surname will be shown, if forename is NULL.

Regards

Robert

#3

Hi Robert,
Thanks for answering.
Your suggestion, however, do not work on my Opensuse Leap 42.3 /
LibreOffice 5.3.3.2 configuration.

I use the Query design feature to enter

"Vorname" || IFNULL( ' & ' || "Partnervorname", ' ' ) || "Nachname"
 or

"Nachname" || IFNULL( ', ' || "Vorname", ' ')

Both above return 0 (yes, zero!) for every record.
Only the "Partnervorname" field has NULL values. I didn't expect
concatenation to be treated like 'multiplication', but I learn
something new every day! Neverthless, something is not right here.

However, 
COALESCE ( CONCAT( "Vorname", SUBSTR( ' & ', 1 ), "Partnervorname",
SPACE( 1 ), "Nachname" ), CONCAT( "Vorname", SPACE( 1 ), "Nachname" ) )

delivers the required result.

Cheers
Harvey

#4

Hi Harvey,

Hi Robert,
Thanks for answering.
Your suggestion, however, do not work on my Opensuse Leap 42.3 /
LibreOffice 5.3.3.2 configuration. >
I use the Query design feature to enter

"Vorname" || IFNULL( ' & ' || "Partnervorname", ' ' ) || "Nachname"
 or

"Nachname" || IFNULL( ', ' || "Vorname", ' ')

Both above return 0 (yes, zero!) for every record.
Only the "Partnervorname" field has NULL values. I didn't expect
concatenation to be treated like 'multiplication', but I learn
something new every day! Neverthless, something is not right here.

However, 
COALESCE ( CONCAT( "Vorname", SUBSTR( ' & ', 1 ), "Partnervorname",
SPACE( 1 ), "Nachname" ), CONCAT( "Vorname", SPACE( 1 ), "Nachname" ) )

delivers the required result.

Which database do you use? Internal HSQLDB? My system here: OpenSUSE
42.2, LO 5.4.1.2. Have tested all this code for the Base-Handbook, so I
am interested why it shouldn't work for you. Must say I didn#t test how
it would work when entering the code in GUI mode, not switching to SQL mode.

Regards

Robert

#5

Hi Harvey,

>
> Hi Robert,
> Thanks for answering.
> Your suggestion, however, do not work on my Opensuse Leap 42.3 /
> LibreOffice 5.3.3.2 configuration. >
> I use the Query design feature to enter
>
> "Vorname" || IFNULL( ' & ' || "Partnervorname", ' ' ) || "Nachname"
>  or
>
> "Nachname" || IFNULL( ', ' || "Vorname", ' ')
>
> Both above return 0 (yes, zero!) for every record.
> Only the "Partnervorname" field has NULL values. I didn't expect
> concatenation to be treated like 'multiplication', but I learn
> something new every day! Neverthless, something is not right here.
>
> However, 
> COALESCE ( CONCAT( "Vorname", SUBSTR( ' & ', 1 ), "Partnervorname",
> SPACE( 1 ), "Nachname" ), CONCAT( "Vorname", SPACE( 1 ), "Nachname"
> ) )
>
> delivers the required result.

Which database do you use? Internal HSQLDB? My system here: OpenSUSE
42.2, LO 5.4.1.2. Have tested all this code for the Base-Handbook, so
I
am interested why it shouldn't work for you. Must say I didn#t test
how
it would work when entering the code in GUI mode, not switching to
SQL mode.

Regards

Robert
-- 
Homepage: http://robert.familiegrosskopf.de
LibreOffice Community: http://robert.familiegrosskopf.de/map_3

I have a backend Mariadb 10 which I access directly using the using the
MySQL connector.

Cheers
Harvey

#6

Hi Harvey,

I have a backend Mariadb 10 which I access directly using the using the
MySQL connector.

All right. With MariaDB (and MySQL) the two pipes (||) dont work.

But something like
SELECT CONCAT("Vorname, ' ',IFNULL("Partnervorname",' '),"Nachname") AS
"Name" FROM "Table"

should work.

Regards

Robert

#7

Hi Robert,
 you are right it works with CONCAT, but, isn't CONCAT inconsistent
here?

This seems to work as desired:

CONCAT( [Vorname], IFNULL( CONCAT( ' & ', [Partnervorname] ), '' ), '
', [Nachname] )

But shouldn't the CONCAT( ' & ', [Partnervorname] ), '' ) actually
return a NULL due to the '' (i.e. not a space, zero length)?

Cheers
Harvey

#8

Hi Harvey,

But shouldn't the CONCAT( ' & ', [Partnervorname] ), '' ) actually
return a NULL due to the '' (i.e. not a space, zero length)?

NULL: There is nothing inside, no string, no number, no data ...
Empty: Could only be for strings - nothing to see, but its a string with
zero length.

Base will save for default all fields without content as NULL, not as
fields, which are empty. The difference: You could concatenate empty
fields with content of other fields without problems, because it
recognizes an empty string. With NULL it won't work.

The Help in LibreOffice and the GUI is missleading here, because Base
won't save fields as empty fields but as NULL.

Regards

Robert

#9

Thanks, Robert. I had a feeling that would be the answer. Many thanks
for helping me to clear that up. Your proposed syntax is certainly more
economical than the COALESCE alternative.

Cheers
Harvey

#10

Hi Robert,
....but as I now have discovered, the COALESCE command is the correct
solution to cope with several alternative syntax combinations by
concatenation. Each CONCAT expression in the COALESCE parameter list
can represent one of the desired syntax combinations, always assuming
that the successive expressions in the parameter list can 'disqualify'
themselves by returning a NULL from CONCAT before reaching the desired
combination. It strikes me as being quite an elegant solution.

Many thanks for your help.

Cheers
Harvey