STUFF - Insert character after certain field

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)

STUFF - Insert character after certain field

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2014-06-12 : 13:58:32

The following works fine but there should be a comma (,) after the COMPANY_STATE field:


	DECLARE @Address VARCHAR(564)

	SET @Address = (
	 	
		SELECT
       STUFF((SELECT ' ' + c.Value + ' ' FROM Constants c
			WHERE c.Name IN ('COMPANY_ADDR1', 'COMPANY_CITY', 
				'COMPANY_STATE', 'COMPANY_ZIP') 
            FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')             
            
	
	)	
	SELECT @Address

I have been searching around to find how to add a comma only after the COMPANY_STATE field.

Any suggestions are welcomed.

Thanks!!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-12 : 14:11:25

use a case statement e.g. in your subquery


select ' ' + c.value + case when c.name = 'Company_State' then ',' else '' end

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2014-06-12 : 14:14:49

That did the trick. Should have thought of that.

Thanks!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources