Replace value - 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 Programming

Replace value

Author

Topic

Lynx
Starting Member

4 Posts

Posted - 2010-05-30 : 04:48:17

Hi,

I want to replaced a value inside a string. The table look like this:
Id Value
1 34,52,68
2 48,34,66

How do I replace the value 34 with 99 to make the table look like this:
Id Value
1 99,52,68
2 48,99,66

Is it possible, and how do I do it?

Thanks in advance!
Carl

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-30 : 05:05:17

[code]
update t
set Value = substring (NewValue, 2, len(NewValue) - 2)
from (
select Value,
NewValue = replace(',' + Value + ',', ',34,', ',99,')
from yourtable
) t
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Lynx
Starting Member

4 Posts

Posted - 2010-05-30 : 06:21:21

Hi,

Thank you!

You saved my day!

Carl

Sachin.Nand

2937 Posts

Posted - 2010-05-30 : 06:22:15

Update yourtable set value=REPLACE(value,'34','99')

PBUH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-30 : 09:41:33

quote:
Originally posted by Idera

Update yourtable set value=REPLACE(value,'34','99')

PBUH

that will update 34,134 to 99,199

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2010-05-30 : 10:12:32

Possibly add:


WHERE ',' + Value + ',' LIKE '%,34,%'

to reduce the number of rows updated (and LOG space used) to just those that qualify

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-30 : 11:13:34

quote:
Originally posted by Kristen

Possibly add:

WHERE ',' + Value + ',' LIKE '%,34,%'

to reduce the number of rows updated (and LOG space used) to just those that qualify

Good point. I missed that

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-31 : 03:53:23

This is one reason why you need to apply normalisation