help in writing update sql query

Question

i want to write an update statement that has numbers, its a varchar datatype. I want to replace each occurrence of a number with different number. For ex. 0 will be replaced by 1, 1 will be replaced by 5 , 2 will be replaed by 7 and so on.

So if a row with value of 012, should be updated to 157.

Can someone help me write an update for this, it has to be an efficient update statement as i have a large number of rows to be updated.

Answer 1

For your previous example that does not work, check an alternative:

select replace(replace(replace(replace(replace(replace(replace(replace(
    '01234516',
    '0', char(6+16)),
    '1', char(8+16)),
    '2', char(7+16)), 
    '6', char(9+16)),
    char(6+16), '6'),
    char(8+16), '8'),
    char(7+16), '7'), 
    char(9+16), '9')
    AS Replaced

Answer 2

Check the efficiency of this statement:

 update MyTable set Column1 = translate(Column1, '012', '157')

Answer 3

im on SQL 2016, so this function is not avaailable.

Answer 4

I tried using REPLACE function, but its not working as expected.
For ex:

SELECT replace(REPLACE(REPLACE(REPLACE('01234516','0','6'),'1','8'),'2','7'), '6','9') AS Replaced;

Gives me output as 98734589 -- I want first number 0 to be replaced by 6, it should not be 9, due to nested replace it is replacing 0 to 6 first and then to 9 which is incorrect.

Correct output should be 68734589

CURRENTLY IM ACHIEVING THIS USING USER DEFINED FUNCTION, BUT ITS VERY SLOW.

Answer 5

Hi @SQLServerBro,

For this data, you only need to change the replacement order:

SELECT replace(REPLACE(REPLACE(REPLACE('01234516','6','9'),'1','8'),'2','7'), '0','6')  
AS Replaced;  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.