This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(3.2, 54%, 10%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EH%3C/text%3E%3C/svg%3E)
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.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(96, 26%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYK%3C/text%3E%3C/svg%3E)
While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
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
Viorel,
Thanks for the answer, seems like it is working perfectly, can you explain the logic behind this? also let me know how can i be good in writing logic this just like you :)
EchoLiu-msft , your logic is not working if i ve a number like this 69234516.
For example, to replace ‘1’ with ‘2’ and ‘2’ with ‘1’, it is not possible to use two REPLACE functions directly, because the second function will incorrectly replace the results of the first one.
In the proposed approach, instead of replacing ‘1’ with ‘2’, it is replaced with a special intermediate code which is not used for normal characters. The range of ten control codes between 16 and 25 is used for these purposes. The convention is: the digit ‘D’ will be represented by the code D+16.
The statement is: REPLACE(REPLACE(REPLACE(REPLACE(sometext, '1', char(2+16)), '2', char(1+16)), char(2+16), '2'), char(1+16), '1').
Therefore, instead of replacing the characters ‘1’ (code 49) with ‘2’, it is replaced with the code 2+16, i.e. 18. Then ‘2’ (code 50) is replaced with 1+16, i.e. 17. Then other REPLACE functions convert these codes to normal digits: 18 is replaced with ‘2’, 17 is replaced with ‘1’.
As a result, ‘1’ was replaced with ‘2’ and ‘2’ was replaced with ‘1’ without interferences.
Thank you for the explaination,
So i can also write query like this correct. this will replace 1 with 2 and 2 with 1
SELECT replace (replace(
REPLACE(REPLACE('01234016','1','XX'),'2','XY'), 'XX', '2'), 'XY', '1') AS Replaced;
If your text does not contain 'XX' and 'XY' (or contains numbers only), then you can use intermediate letters.
Got it, thank you. Appreciate all the feedback and a brilliant query.