Solved

Add comma after every letter in a string

Posted on 2011-09-29
5
193 Views
Last Modified: 2012-05-12
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
Comment
Question by:angelnjj
  • 3
5 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36817921
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
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
ID: 36817924
try:

right(Items,1)
-->
'''' + right(Items,1) + ''', '
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36817933
it should give you

 'N', 'S', 'O', 'K',
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 500 total points
ID: 36817955
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
 
LVL 1

Author Closing Comment

by:angelnjj
ID: 36818015
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now