add comma to a string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

add comma to a string

Author

Topic

jeff06
Posting Yak Master

166 Posts

Posted - 2007-12-07 : 15:05:44

I have strings in a table:

JACKSONVILLE NC
DAVENPORT-MOLINE-ROCK ISLAND IA-IL
SPRINGFIELD MA
ATLANTA GA
GOLDSBORO NC
Washington DC-MD-VA-WV
-----

I want add a comma to them the result should look like:
JACKSONVILLE, NC
DAVENPORT-MOLINE-ROCK ISLAND, IA-IL
SPRINGFIELD, MA
ATLANTA, GA
GOLDSBORO, NC
Washington, DC-MD-VA-WV
---
How can I do that
Thanks

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-12-07 : 15:51:35

replace (reverse([Fieldname]),' ', ',')

Poor planning on your part does not constitute an emergency on my part.

jeff06
Posting Yak Master

166 Posts

Posted - 2007-12-10 : 09:09:01

datagurun1971.

Thanks you for your suggestions.

I get some thing like
LI, DNALIS, KCOR-ENILOM_TROPNEVAD

for DAVENPORT-MOLINE-ROCK ISLAND IA-IL
How can i fix this problem?
THX
But I want DAVENPORT-MOLINE-ROCK ISLAND, IA-IL

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-10 : 09:15:49

reverse(replace (reverse([Fieldname]),' ', ','))

Madhivanan

Failing to plan is Planning to fail

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-10 : 09:46:37

Why not just

 
replace([Fieldname],' ', ', ')

jeff06
Posting Yak Master

166 Posts

Posted - 2007-12-10 : 09:57:16

The problen is some addresses contain several spaces in words. I just want to repace the last one with ','
I solved the problem with:

stuff(Fieldname],len(Fieldname],)-CHARINDEX(' ', reverse(Fieldname])),1,',')

Thanks all for help.

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-12-12 : 00:56:02

REVERSE(LEFT(REVERSE(FieldName), CHARINDEX(' ', REVERSE(FieldName))) + ',' + RIGHT(REVERSE(FieldName), LEN(REVERSE(FieldName)) - CHARINDEX(' ', REVERSE(FieldName))))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources