Concat with null string

Creating tables and queries

Concat with null string

Postby rforester » Fri Dec 21, 2012 10:32 pm

I am trying to concatenate two fields in a query, but if one of the tow is empty, it is not returning anything.
Here is what I am using:
CONCAT( "OADDRESS1", "OADDRESS2" )

When OADDRESS2 is empty, it is returning an empty field.
How can I bring just OADDRESS1 when OADDRESS2 is empty?

Thanks!
Open Office 3.4.1 on Windows XP
rforester
 
Posts: 2
Joined: Fri Dec 21, 2012 9:55 pm

Re: Concat with null string

Postby Sliderule » Fri Dec 21, 2012 11:40 pm

Use EITHER ONE of the two statements below:

Code: Select all   Expand viewCollapse view

CONCAT( "OADDRESS1", COALESCE("OADDRESS2",'') )


"OADDRESS1" || COALESCE("OADDRESS2",'')


Explanation: The SQL function COALESCE will return the second value ( in this case, a string with a lengh of zero :bravo: ) when the first value is NULL ( UNDEFINED ). You can read about the COALESCE function at:

http://www.hsqldb.org/doc/1.8/guide/ch09.html

http://www.hsqldb.org/doc/1.8/guide/ch09.html wrote:
COALESCE(expr1,expr2,expr3,...)

if expr1 is not null then it is returned else, expr2 is evaluated and if not null it is returned and so on

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1170
Joined: Thu Nov 29, 2007 9:46 am

Re: Concat with null string

Postby Villeroy » Fri Dec 21, 2012 11:47 pm

Any arithmetic or logical operation or concatenation with Null returns Null. The Coalesce function handles this by replacing Null with anything appropriate.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26852
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Concat with null string

Postby rforester » Mon Dec 24, 2012 2:33 pm

Thanks for the reply!

It should work but now it says:

The query can not be executed. It is too complex.

Any ideas?
Open Office 3.4.1 on Windows XP
rforester
 
Posts: 2
Joined: Fri Dec 21, 2012 9:55 pm

Re: Concat with null string

Postby FJCC » Mon Dec 24, 2012 3:34 pm

Try setting the option Edit -> Run SQL Command Directly.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7189
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Concat with null string

Postby Villeroy » Mon Dec 24, 2012 11:45 pm

rforester wrote:Thanks for the reply!

It should work but now it says:

The query can not be executed. It is too complex.

Any ideas?

You did not mention the type of database indicated in the status bar. COALESCE runs fine with HSQLDB.

The minimal set of functions for file based databases (dBase, spreadsheets, text etc) does not include COALESCE.
http://www.openoffice.org/dba/specifica ... tions.html
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26852
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 2 guests