Update SQL Server statement

cigdeys3 于 2023-10-15 发布在 SQL Server

关注(0)|答案(1)|浏览(144)

I have a column data with length 11 characters of type varchar . I want to change to 10 characters with condition: change 4 characters to 3 characters base on condition if begin 4 characters is '0169' and then change to '039'.

Example:

DECLARE @Table TABLE (SomeCol VARCHAR(11));

INSERT INTO @Table (SomeCol) 
VALUES ('01691234567'), ('01692234567'), ('01693234567'), 
       ('01694234567'), ('01695234567'), ('11691234567'), 
       ('11692234567'), ('11693234567'), ('11694234567'), 
       ('11695234567');

Expected output:

SomeCol
0391234567
0392234567
0393234567
0394234567
0395234567
11691234567
11692234567
11693234567
11694234567
11695234567

sql-server

来源：https://stackoverflow.com/questions/77113921/update-sql-server-statement

1条答案

按热度按时间

dgenwo3n1#

The following UPDATE statement help you to change column values to 10 characters if the value begins with '0169' using CASE statement. You can include additional WHEN clause inside the CASE statement if you want to change the column value based on any other similar condition.

UPDATE @Table SET SomeCol = CASE LEFT(SomeCol,4) 
                                WHEN '0169' THEN '039'+RIGHT(SomeCol,LEN(SomeCol)- 4) 
                                ELSE SomeCol 
                            END
FROM @Table

赞(0）回复(0）举报 2023-10-15

我来回答

Update SQL Server statement

1条答案

相关问题

热门标签

最新问答