SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Test if null or empty		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Zath
Constraint Violating Yak Guru

USA
255 Posts
Posted - 11/07/2012 :  14:17:18  Show Profile  Visit Zath's Homepage  Reply with Quote

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
Yak Posting Veteran

72 Posts
Posted - 11/07/2012 :  14:28:15  Show Profile  Reply with Quote
SELECT col1
,CASE WHEN col3 IS NULL
OR col3 = '' THEN (col2 + ' ' + col4)
ELSE (col2 + ' ' + col3 + ' ' + col4)
END AS MyCol
FROM MyTable
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/07/2012 :  17:08:50  Show Profile  Reply with Quote
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;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000