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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Zath
Constraint Violating Yak Guru

USA
298 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
Posting Yak Master

103 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

5155 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  
 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