27,579
社区成员
发帖
与我相关
我的任务
分享
create table TAB(BH varchar(32), ZS varchar(32))
insert TAB(BH) select '7AB2057'
union all select '7AB1805'
union all select '7AB40C-H'
union all select '7C100/A'
union all select '7A607LC'
union all select '7W905/B'
union all select null
union all select '7W'
union all select ''
update TAB
set ZS=case when patindex('%[1-9][0-9][0-9]%',BH)>0
then (case when substring(BH,patindex('%[1-9][0-9][0-9]%',BH),3)>'180'
then substring(BH,patindex('%[1-9][0-9][0-9]%',BH),2)
else substring(BH,patindex('%[1-9][0-9][0-9]%',BH),3) end)
when patindex('%[1-9][0-9]%',BH)>0
then substring(BH,patindex('%[1-9][0-9]%',BH),2)
else ZS end
select * from TAB
drop table TAB
/*
BH ZS
-------------------------------- --------------------------------
7AB2057 20
7AB1805 180
7AB40C-H 40
7C100/A 100
7A607LC 60
7W905/B 90
NULL NULL
7W NULL
NULL
(9 row(s) affected)
*/
declare @a table(BH varchar(20), ZS int)
insert @a select '7AB2057',null
union all select '7AB1805',null
union all select '7AB40C-H', null
union all select '7C100/A', null
union all select '7A607LC', null
union all select '7W90A/B', null
union all select '7W10A/B', null
update aa set zs=case when cast(left(num,2) as int) between 18 and 180 then left(num,2)
when isnumeric(num)=1 and cast(num as int) between 18 and 180 then num end--end
from @a aa,
(
select bh,left(right(bh,len(bh)-patindex('%[0-9][0-9]%',bh)+1),3) num
from @a
where patindex('%[0-9][0-9]%',bh)>0
)bb where aa.bh=bb.bh
select * from @a
--result
/*
BH ZS
-------------------- -----------
7AB2057 20
7AB1805 18
7AB40C-H 40
7C100/A 100
7A607LC 60
7W90A/B 90
7W10A/B NULL
*/
create table TAB(BH varchar(32), ZS varchar(32))
insert TAB(BH) select '7AB2057'
union all select '7AB1805'
union all select '7AB40C-H'
union all select '7C100/A'
union all select '7A607LC'
union all select '7W905/B'
union all select null
union all select '7W'
union all select ''
update TAB
set ZS=case when patindex('%[1-9][0-9][0-9]%',BH)>0
then (case when substring(BH,patindex('%[1-9][0-9][0-9]%',BH),3)>'180'
then substring(BH,patindex('%[1-9][0-9][0-9]%',BH),3)/10
else substring(BH,patindex('%[1-9][0-9][0-9]%',BH),3) end)
when patindex('%[1-9][0-9]%',BH)>0
then substring(BH,patindex('%[1-9][0-9]%',BH),2)
else ZS end
select * from TAB
drop table TAB
/*
BH ZS
-------------------------------- --------------------------------
7AB2057 20
7AB1805 180
7AB40C-H 40
7C100/A 100
7A607LC 60
7W905/B 90
NULL NULL
7W NULL
NULL
(9 row(s) affected)
*/
create table TAB(BH varchar(32), ZS int)
insert TAB(BH) select '7AB2057'
insert TAB(BH) select '7AB1805'
insert TAB(BH) select '7AB40C-H'
insert TAB(BH) select '7C100/A'
insert TAB(BH) select '7A607LC'
insert TAB(BH) select '7W905/B'
update TAB
set ZS=case when patindex('%[1-9][0-9][0-9]%',BH)>0
then substring(BH,patindex('%[1-9][0-9][0-9]%',BH),3)
when patindex('%[1-9][0-9]%',BH)>0
then substring(BH,patindex('%[1-9][0-9]%',BH),2)
else ZS end
update TAB set ZS=ZS/10
where ZS>180
select * from TAB
drop table TAB
/*
BH ZS
-------------------------------- -----------
7AB2057 20
7AB1805 180
7AB40C-H 40
7C100/A 100
7A607LC 60
7W905/B 90
(6 row(s) affected)
*/
--提取数值
update TAB
set ZS=case when patindex('%[1-9][0-9][0-9]%',BH)>0
then substring(BH,patindex('%[1-9][0-9][0-9]%',BH),3)
when patindex('%[1-9][0-9]%',BH)>0
then substring(BH,patindex('%[1-9][0-9]%',BH),2)
else ZS end
--处理大于180的数
update TAB set ZS=ZS/10
where ZS>180