Solved

Change text in a table from val1 to val2 in MSSQL

Posted on 2014-01-30
11
294 Views
Last Modified: 2014-03-03
How would you write a query that would change all occurances in a table of val1 to val2?
0
Comment
Question by:hrolsons
  • 7
  • 3
11 Comments
 
LVL 32

Expert Comment

by:awking00
ID: 39822000
Regardless of the column in which those values exist?
0
 

Author Comment

by:hrolsons
ID: 39822043
Correct, in all columns.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39822054
<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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 32

Expert Comment

by:awking00
ID: 39823981
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
 

Author Comment

by:hrolsons
ID: 39824726
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
 
LVL 32

Expert Comment

by:awking00
ID: 39824880
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
 
LVL 32

Expert Comment

by:awking00
ID: 39824892
Note - if val1 and val2 are strings, the dynamic sql will need to include the extra quotes -
sql = '... replace(' + @colname + ',' ' val1' '.' 'val2' ')'
0
 
LVL 32

Expert Comment

by:awking00
ID: 39824896
Before doing the execution of a dynamic update statement, I would print the sql statement first to insure that the syntax is correct.
0
 

Author Comment

by:hrolsons
ID: 39828136
So the "Select" is working.  How do I commit the changes to the table?
0
 
LVL 32

Accepted Solution

by:
awking00 earned 500 total points
ID: 39829393
@sql = 'update yourtable set ' + @colname + ' = replace(' + @colname + ',val1, val2)'
begin transaction x
exec @sql
commit transaction x
0
 
LVL 32

Expert Comment

by:awking00
ID: 39829396
I'm not sure but execute statement may require parentheses -
exec(@sql)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question