CODEWITHSUNDEEP
sqlsql-update
Riain McAtamney
Riain McAtamney

Reputation: 6432

SQL update column twice

I have a string column (Col1) in table (Tab1). I want to write a script that removes '%' or '&' characters from the string values.

The select for this is the following:

SELECT REPLACE( Tab1.Col1, '&', ''), 
       REPLACE(Tab1.Col1, '%', '')
FROM Table1 Tab1
WHERE Tab1.Col1 like '%[&]%' 
      OR Tab1.Col1 like '%[%]%'

Does this seem correct? If it is how would I convert this to any update statement? I tried the following:

UPDATE Tab1
SET Tab1.Col1 =  REPLACE( Tab1.Col1, '&', ''),  
    Tab1.Col1 = REPLACE (Tab1.Col1, '%', '')
FROM Table1 Tab1
WHERE Tab1.Col1 like '%[&]%' 
      OR Tab1.Col1 like '%[%]%'

This doesn't work as you cant update column twice in SET. Is there any other way I can do this? I know I'm probably doing something silly here so apologies for my ignorance.

Thanks in advance.

Upvotes: 0

Views: 3351

Answers (3)

user533832
user533832

Reputation:

As you don't say what RDBMS you are using, I'm avoiding things that are obviously non-portable:

UPDATE Tab1 
SET Tab1.Col1 = REPLACE(REPLACE(Tab1.Col1, '&', ''), '%', '') 
WHERE Tab1.Col1 <> REPLACE(REPLACE(Tab1.Col1, '&', ''), '%', '');

Upvotes: 0

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25390

 UPDATE Tab1
 SET Tab1.Col1 =  REPLACE(REPLACE( Tab1.Col1, '&', ''), '%', '') 
 FROM Table1 Tab1
 WHERE Tab1.Col1 like '%[&]%' 
  OR Tab1.Col1 like '%[%]%'

Upvotes: 1

The King
The King

Reputation: 4654

This should work. Replace returns a string which you can again pass to another replace function.

UPDATE Tab1 
    SET Tab1.Col1 =  REPLACE(REPLACE( Tab1.Col1, '&', ''), '%', '') 
    FROM Table1 Tab1 
    WHERE Tab1.Col1 like '%[&]%' OR Tab1.Col1 like '%[%]%'

Upvotes: 5

Related Questions