question

Heisenberg avatar image
0 Votes"
Heisenberg asked Heisenberg commented

help in writing update sql query

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.

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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;)

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered

Check the efficiency of this statement:

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Heisenberg avatar image
0 Votes"
Heisenberg answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Heisenberg avatar image
0 Votes"
Heisenberg answered Heisenberg edited

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @SQLServerBro,

126581-image.png

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:
126591-image.png

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.



image.png (30.7 KiB)
image.png (1.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Heisenberg avatar image
0 Votes"
Heisenberg answered Heisenberg commented

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.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


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.


0 Votes 0 ·

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;

0 Votes 0 ·

If your text does not contain 'XX' and 'XY' (or contains numbers only), then you can use intermediate letters.

1 Vote 1 ·
Show more comments