SQL Server : update replace statement - code fail or bug?

Question

I am trying to replace all letters in string with "L" and all the number with letter "N" in SQL Server 2014.

I am using following statement:

update my_table 
set column2 = replace(column1, 'a', 'L');

update my_table 
set column2 = replace(column1, 'b', 'L');

update my_table 
set column2 = replace(column1, '1', 'N');
etc. etc.

The problem is, when I execute the query I get that column2 = column1 e.g. abc(column1) = abc(column2)

If I query only one commit e.g. update my_table set column2 = replace(column1, 'a', 'L'); then it works fine, it replaces all A to L.

I tried to excute this query with Sqlite and tested it with smaller database and it works just fine, but i would really need to make this work in mssql.

I would really highly appreciate any support with this question.

I have just started to use SQL Server and I still trying to adopt to changes.

p.s. column2 varchar(64)

Arion · Accepted Answer · 2016-02-24 10:46:15Z

1

This is what I think happens in your code. Consider the column2 to be '123456' and column1 to be 'ab1'. So then you run the first update:

update my_table set column2 = replace(column1, 'a', 'L');

column1 = ab1
column2 = Lb1

The second update:

update my_table set column2 = replace(column1, 'b', 'L');

column1 = ab1
column2 = aL1

The third update:

update my_table set column2 = replace(column1, '1', 'N');

column1 = ab1
column2 = abN

So because you never change column1 you will always take the "latest version" and update it. So how to fix this. One way which is not nice is like this:

update my_table set column2 = 
  replace(
          replace(
                replace(column1, 'a', 'L'),
          'b', 'L'), 
  '1', 'N');

column1 = ab1
column2 = LLN

answered Feb 24, 2016 at 10:46

Arion

31.3k10 gold badges75 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mssqlw Over a year ago

Thank you for your answer. I'll try your code and see what it returns:)

marc_s · Accepted Answer · 2016-02-24 12:46:18Z

0

I don't know if you have a fixed length data in your column_1, in case it's fixed you can use the following:

declare @Index int=1

while(@Index<= your data length here)
begin
    update my_table 
    set column_2 = REPLACE(substring(column_1, @Index, 1),
                           case 
                              when ASCII(substring(column_1, @Index, 1)) between 48 and 57 
                                then 'N' 
                                else 'L' 
                           end)

    set @Index = @Index + 1
end

edited Feb 24, 2016 at 12:46

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Feb 24, 2016 at 11:18

Shawky

364 bronze badges

1 Comment

Shawky Over a year ago

Need to distinguish between first update and the others, cause the others should update column_2 and not 1

Collectives™ on Stack Overflow

SQL Server : update replace statement - code fail or bug?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related