Add comma after every letter in a string
Posted on 2011-09-29
Using SQL2008, I have a parameter passed to a stored procedure. The parameter passed over is 'Austin, Dallas, Chicago, New York' (just an example, and in our data no need to worry that the last letter would repeat)
I need to use the last character in each word in the where clause of a select statement within the stored procedure.
I've got a way to get the last letter (below, but could probably be simplified)
DECLARE @STRING VARCHAR(8000);
SET @STRING = 'Austin, Dallas, Chicago, New York' ;
declare @string2 varchar (200)
SET @string2 = (SELECT (SELECT right(Items,1)
FROM crp_ReportsReferenceTables.dbo.split2(@String, ',')
FOR XML PATH(''),TYPE).value('.','varchar(max)'))
This returns: 'NSOK'
BUT...How do I get my @string2 to be: 'N', 'S', 'O', 'K' so that I can put that in my where clause?
ie select * from table where [lastcharcity] in (@string2)
Thank you in advance...