Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

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...
0
angelnjj
Asked:
angelnjj
  • 3
2 Solutions
 
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:
try:

right(Items,1)
-->
'''' + right(Items,1) + ''', '
0
 
HainKurtSr. System AnalystCommented:
it should give you

 'N', 'S', 'O', 'K',
0
 
HainKurtSr. 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now