34,590
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE table1
(
KK VARCHAR(10) NOT NULL
)
INSERT INTO table1
SELECT 'AB1' UNION
SELECT 'BC2' UNION
SELECT 'CD3'
SELECT CAST((UNICODE(LEFT(KK,1)) - 64) AS VARCHAR(10)) + CAST((UNICODE(SUBSTRING(KK,2,1)) - 64) AS VARCHAR(10)) + RIGHT(KK,1) AS Num
FROM Table1
select
replace(replace(kk,'A','1'),'B','2') as kk -- 需要的话继续套,呵呵
from tab
create table tb(KK VARCHAR(3))
insert into tb values('AA1')
insert into tb values('AA3')
insert into tb values('BA3')
insert into tb values('CD8')
insert into tb values('CD1')
insert into tb values('CA3')
go
update tb
set kk =
(case when substring(kk,1,1) = 'A' then '1'
when substring(kk,1,1) = 'B' then '2'
when substring(kk,1,1) = 'C' then '3'
when substring(kk,1,1) = 'D' then '4'
end) +
(case when substring(kk,2,1) = 'A' then '1'
when substring(kk,2,1) = 'B' then '2'
when substring(kk,2,1) = 'C' then '3'
when substring(kk,2,1) = 'D' then '4'
end) +
substring(kk,3,1)
select * from tb
/*
KK
----
111
113
213
348
341
313
(所影响的行数为 6 行)
*/
drop table tb