Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Convert comma to single quotes Expand / Collapse
Author
Message
Posted Friday, August 17, 2012 4:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 8, 2013 5:13 AM
Points: 67, Visits: 269
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

Post #1346452
Posted Friday, August 17, 2012 4:29 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 27, 2014 4:17 AM
Points: 75, Visits: 443
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
Twitter: @SomewereSomehow
Post #1346460
Posted Friday, August 17, 2012 4:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 7:27 AM
Points: 2,836, Visits: 5,067
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1346472
Posted Friday, August 17, 2012 5:32 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 27, 2014 4:17 AM
Points: 75, Visits: 443
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
Twitter: @SomewereSomehow
Post #1346484
Posted Friday, August 17, 2012 6:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 7:27 AM
Points: 2,836, Visits: 5,067

...
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1346495
Posted Friday, August 17, 2012 6:25 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 27, 2014 4:17 AM
Points: 75, Visits: 443
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
Twitter: @SomewereSomehow
Post #1346502
Posted Friday, August 17, 2012 6:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:17 AM
Points: 7,225, Visits: 13,700
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1346508
Posted Friday, August 17, 2012 8:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 8, 2013 5:13 AM
Points: 67, Visits: 269
Hi Experts;


Good Thanks for all support

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

Thanks
FAJ
Post #1346569
Posted Friday, August 17, 2012 8:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:17 AM
Points: 7,225, Visits: 13,700
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1346579
Posted Wednesday, October 9, 2013 4:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 14, 2014 3:54 AM
Points: 121, Visits: 138
Tnx It worked for me perfectly.
Post #1503006
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse