Convert comma to single quotes

Question

Post reply

Convert comma to single quotes

sqlfriends@sql.com

SSCommitted

Points: 1504
More actions
August 17, 2012 at 4:15 am

#260213

Hi Experts:
This is my table
Condition Columnname
akraft,crunckel TN
AL,AZ State
Atlanta,Austin-San Marcos MAC
aney,aventura Area
Alexandria, VA,Arlington, TX Market
Alpharetta,Alexandria City
001,002 StoreS
SameStore,HeitmanI Store Type
InLast6Months,CS-CC Space Types
I want to convert the column like as

Condition Columnname
'akraft','crunckel' TN
'AL','AZ' State
'Atlanta','Austin-San Marcos' MAC
'aney','aventura' Area
'Alexandria', 'VA','Arlington', 'TX' Market
'Alpharetta','Alexandria' City
'001','002' Stores
'SameStore','HeitmanI' StoreType
'InLast6Months','CS-CC' SpaceTypes
To add the single quotation to start and End of the position .. any one help to achieve this goal
Thanks
FAJ

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

SomewhereSomehow SSCommitted Points: 1586 More actions · Answer 1

Try using replace function like this:

declare @a varchar(100) = 'akraft,crunckel';

set @a = ''''+replace(@a,',',''',''')+''''

select @a

I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
Blog: http://somewheresomehow.ru[/url]
Twitter: @SomewereSomehow

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

SomewhereSomehow (8/17/2012)
Try using replace function like this:
declare @a varchar(100) = 'akraft,crunckel';
set @a = ''''+replace(@a,',',''',''')+''''
select @a

The above will not produce exact expected results for some of the cases:

Alexandria, VA,Arlington, TX

will be converted to

'Alexandria',' VA','Arlington',' TX'

and if I'm right the OP wouldn't want trailing spaces in ' VA' and ' TX'...

so, to make it closer to the expected one he can do:

declare @a varchar(500)

set @a = 'Alexandria, VA,Arlington, TX'

set @a = replace(''''+replace(@a,',',''',''')+'''',''','' ',''',''')

select @a

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

SomewhereSomehow SSCommitted Points: 1586 More actions · Answer 3

The above will not produce exact expected results for some of the cases:

Yes, but in general your solution won't too. Imagine a few more spaces at the end or begining.

declare @a varchar(500)

set @a = ' Alexandria , VA , Arlington , TX '

set @a = replace(''''+replace(@a,',',''',''')+'''',''','' ',''',''')

select @a

If we are talking about timming spaces or doing smth else with the elements, it would be better to split string, do manipulations and concat it back.

I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
Blog: http://somewheresomehow.ru[/url]
Twitter: @SomewereSomehow

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

...
If we are talking about timming spaces or doing smth else with the elements, it would be better to split string, do manipulations and concat it back.

or use well known way of removing duplicate spaces (http://www.sqlservercentral.com/articles/T-SQL/68378/) with one more check...

declare @a varchar(500)

set @a = ' Alexandria , VA , Arlington , TX '

set @a = REPLACE(''''+REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(@a)),' ',' '+CHAR(7)),CHAR(7)+' ',''),CHAR(7),''),' , ', ','),',',''',''')+'''',''','' ',''',''')

select @a

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

SomewhereSomehow SSCommitted Points: 1586 More actions · Answer 5

Good point!

I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
Blog: http://somewheresomehow.ru[/url]
Twitter: @SomewereSomehow

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

sqlfriends@sql.com (8/17/2012)
Hi Experts:
This is my table
Condition Columnname
akraft,crunckel TN
AL,AZ State
Atlanta,Austin-San Marcos MAC
aney,aventura Area
Alexandria, VA,Arlington, TX Market
Alpharetta,Alexandria City
001,002 StoreS
SameStore,HeitmanI Store Type
InLast6Months,CS-CC Space Types
I want to convert the column like as

Condition Columnname
'akraft','crunckel' TN
'AL','AZ' State
'Atlanta','Austin-San Marcos' MAC
'aney','aventura' Area
'Alexandria', 'VA','Arlington', 'TX' Market
'Alpharetta','Alexandria' City
'001','002' Stores
'SameStore','HeitmanI' StoreType
'InLast6Months','CS-CC' SpaceTypes
To add the single quotation to start and End of the position .. any one help to achieve this goal
Thanks
FAJ

Do you have any control over the design of this table? Your request suggests that you do...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sqlfriends@sql.com SSCommitted Points: 1504 More actions · Answer 7

Hi Experts;

Good Thanks for all support

ChrisM : we don't have using any control to design this table.

Thanks

FAJ

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

sqlfriends@sql.com (8/17/2012)
Hi Experts;
Good Thanks for all support
ChrisM : we don't have using any control to design this table.
Thanks
FAJ

What you have there is an EAV (entity-attribute-value) table. Simple-talk (site currently down) have an excellent discussion of the pitfalls of this design.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Dheeraj Gadave SSC Veteran Points: 283 More actions · Answer 9

Dheeraj Gadave

SSC Veteran

Points: 283

October 9, 2013 at 4:24 am

#1657061

Tnx It worked for me perfectly.

Dwain Camps SSC Guru Points: 86908 More actions · Answer 10

Here's another way that doesn't worry much about how many spaces are included.

WITH SampleData (Condition, Columnname) AS

(

SELECT 'akraft,crunckel','TN'

UNION ALL SELECT 'AL,AZ','State'

UNION ALL SELECT 'Atlanta,Austin-San Marcos','MAC'

UNION ALL SELECT 'aney,aventura','Area'

UNION ALL SELECT 'Alexandria, VA,Arlington, TX','Market'

UNION ALL SELECT 'Alpharetta,Alexandria','City'

UNION ALL SELECT '001,002','StoreS'

UNION ALL SELECT 'SameStore,HeitmanI','Store Type'

UNION ALL SELECT 'InLast6Months,CS-CC','Space Types'

),

SplitStrings AS

(

SELECT *

FROM SampleData

CROSS APPLY dbo.PatternSplitCM(Condition, '[, ]')

)

SELECT Condition, ColumnName

,NewCondition=

(

SELECT CASE [Matched] WHEN 0 THEN ''''+Item+'''' ELSE ',' END

FROM SplitStrings b

WHERE a.Condition = b.Condition AND a.ColumnName = b.ColumnName

ORDER BY ItemNumber

FOR XML PATH('')

)

FROM SplitStrings a

GROUP BY Condition, ColumnName;

PatternSplitCM can be found and is explained in the 4th article in my signature links.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 11

sqlfriends@sql.com (8/17/2012)
I want to convert the column like as

Sometimes a better solution can be made if we know the reason why you would want to do such a thing. What do you intend to do with the data once it's in the format you want?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)