[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 751
  • Last Modified:

Using COALESCE to ignore NULL and empty values

Hi everyone,

I have stumbled across the coalesce function when trying to write a query that will concatenate 4 column values together. The four columns could have different combinations of NULL and/or empty values. For example ...

Col Name - jointAppelation1     jointAppelation2    jointAppelation3     jointAppelation4
Values           name1                        NULL                     NULL                     name2
                        NULL                         name1                                                name2
                      name1                                                                                    name2

I would like to bring these fields together to create a column that shows the values that are not null or empty, split by commar, for example - name1, name2

This is where I am so far based on an example I found on here ...

 
SELECT 
COALESCE(nullif(rtrim(jointAppellation1), '') + ',' ,'') + ' ' 
+ COALESCE(nullif(rtrim((jointAppellation2), '') + ',' ,'') + ' '
+ COALESCE(nullif(rtrim((jointAppellation3), '') + ',' ,'') + ' '
+ COALESCE(nullif(rtrim((jointAppellation4), '') AS [Joint Applicants]
FROM AccountDetails
WHERE ID = '123456'

Open in new window


I am getting an error saying the rtrim function requires 1 argument?

Hope someone can help. Thanks for your time advance.

0
DaiWilliams
Asked:
DaiWilliams
1 Solution
 
AielloJCommented:

Your syntax of rtrim((jointAppellation2) is causing it to think you are trying to pass more than one argument on lines 3 through 5 of your sample code.  It needs to look like rtrim(jointAppellation2).
0
 
JoeNuvoCommented:
the first rtrim you missing one (

SELECT
  COALESCE(nullif(rtrim(jointAppellation1), '') + ',' ,'') + ' ' <-- this line  , rtrim need 1 more (
+ COALESCE(nullif(rtrim((jointAppellation2), '') + ',' ,'') + ' '
+ COALESCE(nullif(rtrim((jointAppellation3), '') + ',' ,'') + ' '
+ COALESCE(nullif(rtrim((jointAppellation4), '') AS [Joint Applicants]
FROM AccountDetails
0
 
exodusterCommented:
Hi.

And this is the truth :)

I think the NULLIF() function is not needed.

Just use help = Books OnLine = BOL.

COALESCE(exp [,...n])  returns first not null expression.

Oh, just use BOL :>

Good luck.
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
JoeNuvoCommented:
exoduster:

DaiWilliams want to get rid of empty string also,
that why he uses NULLIF to return null if field content (after rtrim) equal to '' (empty string)
0
 
gsiricCommented:
Here is working SQL

select decode(substr(result,length(result),1),',',substr(result,1,length(result)-1),result) from (
SELECT 
  decode(rtrim(jointAppellation1),null,'', rtrim(jointAppellation1) ||  ',')  || 
  decode(rtrim(jointAppellation2),null,'', rtrim(jointAppellation2) ||  ',')  ||
  decode(rtrim(jointAppellation3),null,'', rtrim(jointAppellation3) ||  ',')  ||
  rtrim(jointAppellation4) as result
from AccountDetails);

Open in new window

0
 
gsiricCommented:
ups i tought it was Oracle - ignore my last comment
0
 
JoeNuvoCommented:
seem I'm mistake about open bracket
below code should be correct open/close pair
SELECT 
  COALESCE(nullif(rtrim(jointAppellation1), '') + ',' ,'') + ' ' 
+ COALESCE(nullif(rtrim(jointAppellation2), '') + ',' ,'') + ' '
+ COALESCE(nullif(rtrim(jointAppellation3), '') + ',' ,'') + ' '
+ COALESCE(nullif(rtrim(jointAppellation4), '') ,'') AS [Joint Applicants]
FROM AccountDetails
WHERE ID = '123456'

Open in new window

0
 
DaiWilliamsAuthor Commented:
Thankyou for this so far. I seem to be having an issue when I add other columns to the query and also it doesnt seem to work with the where clause. When I take the where clause out it works. Here is how I am building the query now ...


SELECT id
,AccountNo
,MainAccountHolder
, COALESCE(nullif(rtrim(jointAppellation1), '') + ',' ,'') + ' ' 
+ COALESCE(nullif(rtrim(jointAppellation2), '') + ',' ,'') + ' '
+ COALESCE(nullif(rtrim(jointAppellation3), '') + ',' ,'') + ' '
+ COALESCE(nullif(rtrim(jointAppellation4), '') ,'') AS [Joint Applicants]
FROM AccountDetails
WHERE ID = '123456'

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now