Select first non-blank entry from a list

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Select first non-blank entry from a list

Author

Topic

PhilG
Starting Member

3 Posts

Posted - 2015-01-12 : 05:09:06

Hi There,

I have a problem that I keep coming across in SQL which I can't seem to find a good solution for and I am hoping that someone here might have a good idea that I have not thought of yet. In simple terms, it would be like a COALESCE statement that also skips blank string entries, not just with nulls. I have data from a lot of sources (say telephone numbers from 5 different tables for the sake of argument) some might be empty strings, some might be nulls but I need to find the first entry that actually has some data in it. The best idea I have had so far is to write a UDF which coalesces each entry and checks if it has any data in it or not but this is very restrictive, clunky and you always have to have the same number of inputs so it is far from an ideal solution.

CREATE FUNCTION bCOALESCE (@inp1 nvarchar(max),
@inp2 nvarchar(max) = '',
@inp3 nvarchar(max) = '',
@inp4 nvarchar(max) = '',
@inp5 nvarchar(max) = '')
RETURNS nvarchar(MAX) AS
BEGIN
DECLARE @tv nvarchar(max)
IF COALESCE(@inp1,'') <> '' BEGIN SET @tv = @inp1 END
ELSE IF COALESCE(@inp2,'') <> '' BEGIN SET @tv = @inp2 END
ELSE IF COALESCE(@inp3,'') <> '' BEGIN SET @tv = @inp3 END
ELSE IF COALESCE(@inp4,'') <> '' BEGIN SET @tv = @inp4 END
ELSE IF COALESCE(@inp5,'') <> '' BEGIN SET @tv = @inp5 END
ELSE SET @tv = ''
RETURN @tv
END

This UDF can be used as follows:
SELECT [dbo].[bCOALESCE] (NULL, '', 1, 'A', 'B') AS test1
It will return the first non-blank entry (which is 1) but what if there are 3 or 6 entries.

If anyone can suggest a better way of doing this the I think that it would be something that could be quite beneficial to a lot of other people out there, not to mention me.

Many thanks

Phil

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-12 : 09:16:53

put your source fields in a table then use something like:


select top (1) col1
from tablewithcol1
where col1 is not null and col1 <> ''
order by col1position

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-01-12 : 12:24:58

COALESCE ( NULLIF(col1, ''), NULLIF(col2, ''), NULLIF(col3, ''), NULLIF(col4, ''), NULLIF(col5, '') )

For example:


SELECT first_col_with_value, ...
FROM <table_name>
CROSS APPLY (
    SELECT COALESCE ( NULLIF(col1, ''), NULLIF(col2, ''), NULLIF(col3, ''), NULLIF(col4, ''), NULLIF(col5, '') ) AS first_col_with_value
) AS assign_alias_names
ORDER BY first_col_with_value

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-12 : 12:46:17

quote:
Originally posted by ScottPletcher

COALESCE ( NULLIF(col1, ''), NULLIF(col2, ''), NULLIF(col3, ''), NULLIF(col4, ''), NULLIF(col5, '') )

For example:

SELECT first_col_with_value, ...
FROM <table_name>
CROSS APPLY (
SELECT COALESCE ( NULLIF(col1, ''), NULLIF(col2, ''), NULLIF(col3, ''), NULLIF(col4, ''), NULLIF(col5, '') ) AS first_col_with_value
) AS assign_alias_names
ORDER BY first_col_with_value

@scottpletcher that is what the OP does NOT want to do, I think..."but this is very restrictive, clunky and you always have to have the same number of inputs so it is far from an ideal solution."

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-01-12 : 12:53:07

He was talking specifically about a UDF with those comments.

If there can be more than 5, I'd recommend a CROSS APPLY with a VALUES clause to turn the values into an inline table, then SELECT the first non-blank, non-NULL value that way.

PhilG
Starting Member

3 Posts

Posted - 2015-01-13 : 09:47:07

Thanks Guys,

Sorry for the delay, I got tied up on something else but the NULLIF() worked like a charm and meant that I didn't need to use a UDF which is probably a bonus. The CROSS APPLY is also potentially useful in what I am trying to do but for an entirely different reason so I will look into that one a bit more as well.

Thanks again for the help.
Phil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources