Test if null or empty - SQL Server Forums

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)

Test if null or empty

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2012-11-07 : 14:17:18

Been trying to get this correct just can't get the syntax down.

SELECT col1, (col2 + ' ' + col3 + ' ' + col4) AS MyCol
FROM MyTable

Now, if col3 ISNULL or an empty string, I want to remove the leading space that was input. Remove the ' ' so there is not an extra space there. So it would actually be col2 + ' ' + col4

Suggestions?

Thanks!

shilpash
Posting Yak Master

103 Posts

Posted - 2012-11-07 : 14:28:15

SELECT col1
,CASE WHEN col3 IS NULL
OR col3 = '' THEN (col2 + ' ' + col4)
ELSE (col2 + ' ' + col3 + ' ' + col4)
END AS MyCol
FROM MyTable

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-07 : 17:08:50

If col3 is null, then MyCol will be null; so you should do something like shown below which uses COALESCE function to take care of nulls.

This considers the possibility that any of the 3 columns can have null values. If that is not the case, the expression can be simpler.

This is also extensible. If you have a fourth or fifth column, you can follow the same pattern as the one used for col3 and col4.

SELECT col1, LTRIM
	( 
		COALESCE(col2,'')
		+ COALESCE(' '+NULLIF(col3,''),'')
		+ COALESCE(' '+NULLIF(col4,''),'')
	) AS MyCol
FROM
	MyTable;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources