Solved

Add comma after every letter in a string

Posted on 2011-09-29
5
194 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how to do this MS SQL script? 11 56
How to get the following query to show decimals 3 28
Query Records that don't match 8 34
Access 2010 Query Syntax 5 22
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
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 Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

911 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

23 Experts available now in Live!

Get 1:1 Help Now