Change text in a table from val1 to val2 in MSSQL

How would you write a query that would change all occurances in a table of val1 to val2?
hrolsonsAsked:
Who is Participating?
 
awking00Connect With a Mentor Commented:
@sql = 'update yourtable set ' + @colname + ' = replace(' + @colname + ',val1, val2)'
begin transaction x
exec @sql
commit transaction x
0
 
awking00Commented:
Regardless of the column in which those values exist?
0
 
hrolsonsAuthor Commented:
Correct, in all columns.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<air code>

UPDATE YourTable
SET
   Column1 = CASE Column1 WHEN 'val1' THEN 'val2' END,
   Column2 = CASE Column2 WHEN 'val1' THEN 'val2' END,
   Column3 = CASE Column3 WHEN 'val1' THEN 'val2' END,
   ColumnLast = CASE ColumnLast WHEN 'val1' THEN 'val2' END

Of course, this will only work for those columns that have the same data type as whatever 'val1' and 'val2' is.
0
 
awking00Commented:
Not sure I have the syntax right, but perhaps something like this -
declare @sql varchar(max)
declare @colname varchar(100)
declare col_cursor for
select column_name
from information_schema.columns
where table_name = 'yourtable'
and data_type = <whatever val1 datatype is>

open col_cursor
fetch next from col_cursor into @colname
while @@fetch_status = 0
begin
@sql = 'update yourtable set ' + @colname + ' = replace(' + @colname + ',val1, val2)'
exec @sql
end
close col_cursor
0
 
hrolsonsAuthor Commented:
And, the cell can contain other info.  For instance:

"I love val1 and his brother"

Would change to:

"I love val2 and his brother"
0
 
awking00Commented:
Don't have sql server handy but the same thing's true in most DBMS -
SQL> select replace('I love val1 and his brother','val1','val2') from dual;

REPLACE('ILOVEVAL1ANDHISBRO
---------------------------
I love val2 and his brother
0
 
awking00Commented:
Note - if val1 and val2 are strings, the dynamic sql will need to include the extra quotes -
sql = '... replace(' + @colname + ',' ' val1' '.' 'val2' ')'
0
 
awking00Commented:
Before doing the execution of a dynamic update statement, I would print the sql statement first to insure that the syntax is correct.
0
 
hrolsonsAuthor Commented:
So the "Select" is working.  How do I commit the changes to the table?
0
 
awking00Commented:
I'm not sure but execute statement may require parentheses -
exec(@sql)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.