update tb set col=case when col like 'PM106%' then left(col,len(col)-3) when col not like 'PM106%' then stuff(left(col,len(col)-3),3,1,'') end
select case when substring(col1,1,2)=pm then substring(col1,1,len(col1)-3) else substring(replace(col1,'pm0','pm'),1,len(replace(col1,'pm0','pm'))-3) end,from tb
--> Title : Generating test data [tb] --> Author : --> Date : 2009-12-04 15:08:58 if object_id('[tb]') is not null drop table [tb] go create table [tb] (col nvarchar(24)) insert into [tb] select 'pm1061001123' union all select 'pm0991001123' update tb set col=case when col like 'PM106%' then left(col,len(col)-3) else 'pm'+left(ltrim(cast(right(col,len(col)-2)as int)),len(ltrim(cast(right(col,len(col)-2)as int)))-3) end select * from tb /* col ------------------------ pm1061001 pm991001(2 個資料列受到影響) */
---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([col] varchar(12)) insert [tb] select 'pm1061001123' union all select 'pm0991001123'
---更新--- update tb set col=case when col like 'PM106%' then left(col,len(col)-3) when col not like 'PM106%' then stuff(left(col,len(col)-3),3,1,'') end---查询--- select * from [tb] ---结果--- col ------------ pm1061001 pm991001(所影响的行数为 2 行)
set A= CASE WHEN left(A)='PM106' THEN left(len(A)-3) WHEN left(A)<>'PM106' THEN left(2) + substring(A,4,6) END
select case left(col,5)='pm106' then left(col,len(col)-3) else 'pm'+cast(substring(col,3,len(col)-5) as bigint) end as col from tb
---借树哥的数据 if object_id('[tb]') is not null drop table [tb] go create table [tb]([col] varchar(12)) insert [tb] select 'pm1061001123' union all select 'pm0991001123' select case when left(col,5)='pm106' then left(col,len(col)-3) else 'pm'+ltrim(cast(substring(col,3,len(col)-5) as int)) end as col from tb /*col -------------- pm1061001 pm991001(2 行受影响)*/
忘記 when 忘記 轉換
服务器: 消息 174,级别 15,状态 1,行 8 left 函数要求有 2 个参数。
SELECT CASE WHEN ID LIKE 'pm106%' THEN SUBSTRING(ID,0,LEN(ID)-3) ELSE SUBSTRING(REPLACE(ID,'PM0','PM'),0,LEN(ID)-3) END AS ID FROM (SELECT 'pm1061001123' AS ID UNION ALLSELECT 'pm1061001124' AS ID UNION ALLSELECT 'pm1061001125' AS ID UNION ALLSELECT 'pm1061001126' AS ID UNION ALLSELECT 'pm1061001127' AS ID UNION ALLSELECT 'pm0991001123' AS ID UNION ALLSELECT 'pm0991001124' AS ID UNION ALLSELECT 'pm0991001125' AS ID ) A
SELECT CASE WHEN ID LIKE 'pm106%' THEN SUBSTRING(ID,0,LEN(ID)-2) ELSE SUBSTRING(REPLACE(ID,'PM0','PM'),0,LEN(ID)-3) END AS ID FROM (SELECT 'pm1061001123' AS ID UNION ALLSELECT 'pm1061001124' AS ID UNION ALLSELECT 'pm1061001125' AS ID UNION ALLSELECT 'pm1061001126' AS ID UNION ALLSELECT 'pm1061001127' AS ID UNION ALLSELECT 'pm0991001123' AS ID UNION ALLSELECT 'pm0991001124' AS ID UNION ALLSELECT 'pm0991001125' AS ID ) A
set col=case when col like 'PM106%' then left(col,len(col)-3)
when col not like 'PM106%' then stuff(left(col,len(col)-3),3,1,'')
end
else substring(replace(col1,'pm0','pm'),1,len(replace(col1,'pm0','pm'))-3) end,from tb
--> Author :
--> Date : 2009-12-04 15:08:58
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (col nvarchar(24))
insert into [tb]
select 'pm1061001123' union all
select 'pm0991001123'
update tb set col=case when col like 'PM106%' then left(col,len(col)-3)
else 'pm'+left(ltrim(cast(right(col,len(col)-2)as int)),len(ltrim(cast(right(col,len(col)-2)as int)))-3) end
select * from tb
/*
col
------------------------
pm1061001
pm991001(2 個資料列受到影響)
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(12))
insert [tb]
select 'pm1061001123' union all
select 'pm0991001123'
---更新---
update tb
set col=case when col like 'PM106%' then left(col,len(col)-3)
when col not like 'PM106%' then stuff(left(col,len(col)-3),3,1,'')
end---查询---
select * from [tb]
---结果---
col
------------
pm1061001
pm991001(所影响的行数为 2 行)
CASE
WHEN left(A)='PM106' THEN left(len(A)-3)
WHEN left(A)<>'PM106' THEN left(2) + substring(A,4,6)
END
case left(col,5)='pm106' then left(col,len(col)-3)
else 'pm'+cast(substring(col,3,len(col)-5) as bigint) end as col
from
tb
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(12))
insert [tb]
select 'pm1061001123' union all
select 'pm0991001123'
select
case when left(col,5)='pm106' then left(col,len(col)-3)
else 'pm'+ltrim(cast(substring(col,3,len(col)-5) as int)) end as col
from
tb
/*col
--------------
pm1061001
pm991001(2 行受影响)*/
忘記 轉換
服务器: 消息 174,级别 15,状态 1,行 8
left 函数要求有 2 个参数。
SELECT CASE WHEN ID LIKE 'pm106%' THEN SUBSTRING(ID,0,LEN(ID)-3)
ELSE SUBSTRING(REPLACE(ID,'PM0','PM'),0,LEN(ID)-3) END AS ID FROM (SELECT 'pm1061001123' AS ID
UNION ALLSELECT 'pm1061001124' AS ID
UNION ALLSELECT 'pm1061001125' AS ID
UNION ALLSELECT 'pm1061001126' AS ID
UNION ALLSELECT 'pm1061001127' AS ID
UNION ALLSELECT 'pm0991001123' AS ID
UNION ALLSELECT 'pm0991001124' AS ID
UNION ALLSELECT 'pm0991001125' AS ID
) A
ELSE SUBSTRING(REPLACE(ID,'PM0','PM'),0,LEN(ID)-3) END AS ID FROM (SELECT 'pm1061001123' AS ID
UNION ALLSELECT 'pm1061001124' AS ID
UNION ALLSELECT 'pm1061001125' AS ID
UNION ALLSELECT 'pm1061001126' AS ID
UNION ALLSELECT 'pm1061001127' AS ID
UNION ALLSELECT 'pm0991001123' AS ID
UNION ALLSELECT 'pm0991001124' AS ID
UNION ALLSELECT 'pm0991001125' AS ID
) A