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.
| Author |
Topic |
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
 Posted - 2009-03-10 : 10:52:55
|
| I have a column in a table that is of type varchar. I need to modify the value inside each cell, where applicable. That is, I need to search inside each cell for a particular string and replace said string with a different value.For ex:Let's say the column has a cell with a value of 'ABCdef' and I wish to replace 'def' with 'xxx'. The new string will become 'ABCxxx'. Now, let's say I have a cell with a value of 'ABCxyz'. Because said value does not contain 'def', its value does not change.I may use the REPLACE function, but I'm not selecting this value. I want to update the table and there are multiple cells where it may be applicable, as discussed above.How can I go about making sweeping changes to this column in ad-hoc query. I don't wish to make a stored procedure for this, since this is a one-time change. How may I go about accomplishing this in a query statement?Thank you. |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-03-10 : 10:55:11
|
| I didn't understand your reasoning for not using REPLACE. |
 |
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2009-03-10 : 10:57:49
|
| Oh, no, I'd like to use REPLACE. I'm not just sure how to formulate the sql query to update all appropriate records. Usually, I use REPLACE in a select statement, not an update. |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-03-10 : 11:06:27
|
Nice. You can use it in an update like this,update <yourtable>set <yourcolumn>=replace(<yourcolumn>,<string you want to replace>,<replacement string>) |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-03-10 : 11:07:36
|
| For your above example the UPDATE statement with REPLACE will be like this.UPDATE yourtableSET column = REPLACE(column,'def','xxx')if you some logic to select the rows..then UPDATE yourtableSET column = REPLACE(column,'def','xxx')WHERE column1= your logic and so on. |
|
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2009-03-10 : 11:30:27
|
| Thank you both.Yes, it worked. I was using a nested select statement, which was screwing it up. But now it works! |
|
|
|
|
|
|
|
|
Need to replace certain strings in a table column