?
Solved

Using COALESCE to ignore NULL and empty values

Posted on 2010-09-17
8
Medium Priority
?
734 Views
Last Modified: 2012-05-10
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
Comment
Question by:DaiWilliams
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 13

Expert Comment

by:AielloJ
ID: 33700490

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
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 33700512
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
 
LVL 2

Expert Comment

by:exoduster
ID: 33700524
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 11

Expert Comment

by:JoeNuvo
ID: 33700571
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
 
LVL 7

Expert Comment

by:gsiric
ID: 33700663
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
 
LVL 7

Expert Comment

by:gsiric
ID: 33700686
ups i tought it was Oracle - ignore my last comment
0
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 2000 total points
ID: 33700745
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
 

Author Comment

by:DaiWilliams
ID: 33701050
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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question