Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL replace old values with new ones

Tags:

replace

sql

I have a table named tbl.Products, which has a column named articlenumber and is full of numbers like s401, s402, etc.

I generated a list with new article numbers which will replace the old ones:

s401  I00010  
s402  I00020  
s403  I00030  
s403  I00040  
...  

I have a query from which I hoped that it would work out, but somehow it does nothing.

(of course I have nested the other values into the query)

SELECT REPLACE('articlenumber','s401','I00010') FROM tbl.Products

How do I get a query which replaces old values with new ones in a column like this?

like image 775
elhombre Avatar asked Oct 27 '09 07:10

elhombre


People also ask

How change old value to new value in SQL?

SQL Server REPLACE() Function The REPLACE() function replaces all occurrences of a substring within a string, with a new substring. Note: The search is case-insensitive. Tip: Also look at the STUFF() function.

How do you substitute values in SQL?

The Replace statement is used to replace all occurrences of a specified string value with another string value. The Replace statement inserts or replaces values in a table. Use the Replace statement to insert new rows in a table and/or replace existing rows in a table.

Can we use Replace with update in SQL?

This blog covers using the REPLACE function to selectively replace text inside a string in SQL Server. The REPLACE function is easy to use and also very handy with an UPDATE statement.

How use nested replace in SQL?

SELECT REPLACE(REPLACE(REPLACE(REPLACE('3*[4+5]/{6-8}', '[', '('), ']', ')'), '{', '('), '}', ')'); We can see that the REPLACE function is nested and it is called multiple times to replace the corresponding string as per the defined positional values within the SQL REPLACE function.


2 Answers

Doing a replace can have problems (what if you have an articles s401 and s4010?), therefore it'll be more robust to do it like this:

Update tblProducts
SET articlenumber = 'I000010'
Where articlenumber = 's401';

If you have a number of changes to do, you can either generate a little script (for example with Excel, as TheButcher suggested), or you could import the data into a little table tblVals with the columns oldVal and newVal and then use this statement:

Update tblProducts p
SET articlenumber = (Select newVal
    From tblVals
    where oldVal = p.articlenumber);

This allows you to do the update in one statement which again will be more robust than running a script which may run into problems if it is really long.

A third idea would be to do the logic in constructing the new number for the old number (if such a thing exists) in SQL (or a Stored Procedure) like this:

Update tblProducts p
SET articlenumber = 'I0000' || Right(articlenumber,1) || '0'

(Of course this is totally simplified and probably not nearly sufficient for your 30k rows).

like image 188
Thorsten Avatar answered Oct 27 '22 00:10

Thorsten


You are just selecting the newly replaced values and not doing anything with them... thats a very good idea when using replace, always select first to double check that you will get the expected result :)

The update code -

Update  tbl.Products
Set articlenumber = replace(articlenumber, 's401', 'I00010') 
like image 39
The_Butcher Avatar answered Oct 27 '22 02:10

The_Butcher