Add comma after every letter in a string

Hello All,

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...
LVL 1
angelnjjAsked:
Who is Participating?
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
try:

right(Items,1)
-->
'''' + right(Items,1) + ''', '
0
 
Kevin CrossChief Technology OfficerCommented:
Hi. Are you actually wanting this for use in an IN statement or something? If so, then drop the FOR XML portion as you are already using a split() function to get each word on a row. RIGHT(items, 1) will then work. If you do need this together, but comma-delimited then amend the FOR XML to this:

STUFF((SELECT ', ' + RIGHT(Items, 1) AS "text()"
FROM crp_ReportsReferenceTables.dbo.split2(@String, ',')
FOR XML PATH('')), 1, 2, '')

;)
0
 
HainKurtSr. System AnalystCommented:
it should give you

 'N', 'S', 'O', 'K',
0
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
then use

set @string2 = left(@string2, len(@string2)-1)

example:

declare @string2 varchar (200) = '''N'', ''S'', ''O'', ''K'','
select @string2
set @string2 = left(@string2, len(@string2)-1)
select @string2
0
 
angelnjjAuthor Commented:
The other solution, stripped off the last letter if another city was added (just in case)...the one chosen didn't care how many cities were passed.

Thank you! Thank you!
0
All Courses

From novice to tech pro — start learning today.