string manipulation - 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

General SQL Server Forums

New to SQL Server Administration

string manipulation

Author

Topic

iradev
Starting Member

45 Posts

Posted - 2010-05-28 : 07:10:04

I have a list of locations and some of those locations have a comma after them, e.g."London," or "London, Greater London"
Basically I need to remove the commas from each location ONLY when they are followed by two or more empty spaces. The rest of the locations shouldn't be affected. Any ideas?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-28 : 07:12:53

Easy

UPDATE <theTable> SET
<theColumn> = REPLACE(<theColumn>, ',', '')
WHERE
<theColumn> LIKE '%, %'

Actually this might not be as easy as first assumed.

Can you please post some sample data and indicate exactly what you want to end up with?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-28 : 07:27:01

Maybe this though:


UPDATE <theTable> SET
    <theColumn> = REPLACE(<theColumn>, ',  ', '  ')
WHERE
    <theColumn> LIKE '%,  %'

You could do a simple select


SELECT
    <theColumn> AS [Old Date]
    , REPLACE(<theColumn>, ',', '') AS [New Data]
FROM
    <theTable>
WHERE
    <theColumn> LIKE '%,  %'

To see what will happen.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

iradev
Starting Member

45 Posts

Posted - 2010-05-28 : 09:07:52

Sorry I didn't make myself clear enough the first post. Here is some example data from the column:

text, <--(no space after last comma)
more text, <--(one space after last comma)
and more, text, <--(no space after last comma)
text, some more text, <--(one space after last comma)
more text, text <--(no comma, maybe one space after last word)

What I want to do is remove the commas that are at the end of the words, so I am left with this:

text
more text
and more, text
text, some more text
more text, text

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-28 : 09:15:50

update table
set column = replace(rtrim(column)+'#~@',',#~@','')
where right(rtrim(colum),1)=','

No, you're never too old to Yak'n'Roll if you're too young to die.

iradev
Starting Member

45 Posts

Posted - 2010-05-28 : 09:44:08

thanks again webfred. how can i test the results with a select querry before doing the update?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-28 : 10:15:10

select
column as origColumn,
replace(rtrim(column)+'#~@',',#~@','') as newColumn
from table
where right(rtrim(colum),1)=','

No, you're never too old to Yak'n'Roll if you're too young to die.

iradev
Starting Member

45 Posts

Posted - 2010-05-28 : 10:33:13

Brilliant, much appreciated webfred!

iradev
Starting Member

45 Posts

Posted - 2010-05-28 : 10:43:38

webfred do you mind just telling me the purpose of #~@ ? I couldn't find that info anywhere else.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-28 : 10:52:37

quote:
Originally posted by iradev

webfred do you mind just telling me the purpose of #~@ ? I couldn't find that info anywhere else.

I have used that characters because they probably not already exits in the original data.

I have to replace only the right outer comma if it exists.
So I can't use just replace(column,',','') because it would replace ALL commas.

So I have concatenated '#~@' to the right of the column and then I have replaced the combination of ',#~@' to be sure not to replace some other commas.

Sorry but my english isn't that good to describe it more clear and I hope you could get me

No, you're never too old to Yak'n'Roll if you're too young to die.

iradev
Starting Member

45 Posts

Posted - 2010-05-28 : 10:58:06

Thank you, I understand now :)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-28 : 10:58:49

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-28 : 11:50:20

or

select col,case when col like '%,' then left(col,len(col)-1) else col end as new_col from your_table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources