第3个问题,试试这个: select V , case when v like '%第%[0-9]%号%' then LEFT(v,patindex('%第%[0-9]%号%',v))+ replace(SUBSTRING(v,patindex('%第%[0-9]%号%',v)+1,patindex('%号%',v)),'0','') else null end as vv from ( select 'y000yy第0001号xx000x' as v )t /* V vv y000yy第0001号xx000x y000yy第1号xxx */
有两个问题:1、只有第*号之间的0需要进行操作 2、假如数据是:y000yy第00101号xx000x 结果应该是y000yy第101号xx000x 是这样吗: select V , case when v like '%第0%号%' then LEFT(v,patindex('%第0%号%',v))+ substring(v,patindex('%第0%号%',v)-1+ patINDEX('%[1-9]%',substring(v,patindex('%第0%号%',v),LEN(v))), LEN(v))
else null end as vv from ( select 'y000yy第00101号xx000x' as v )t /* V vv y000yy第00101号xx000x y000yy第101号xx000x */
这个思路不错,这个是第二种方法,通过转化为int,然后再次转化为varchar: select V , case when v like '%第0%号%' then LEFT(v,patindex('%第0%号%',v))+ cast(cast(SUBSTRING(v, patindex('%第0%号%',v)+1, patindex('%号%',v)-patindex('%第%',v)-1) as int) as varchar)+ SUBSTRING(v,patindex('%号%',v),LEN(v))
else null end as vv from ( select 'y000yy第0001号xx000x' as v )t /* V vv y000yy第0001号xx000x y000yy第1号xx000x */
这个思路不错,这个是第二种方法,通过转化为int,然后再次转化为varchar: select V , case when v like '%第0%号%' then LEFT(v,patindex('%第0%号%',v))+ cast(cast(SUBSTRING(v, patindex('%第0%号%',v)+1, patindex('%号%',v)-patindex('%第%',v)-1) as int) as varchar)+ SUBSTRING(v,patindex('%号%',v),LEN(v))
else null end as vv from ( select 'y000yy第0001号xx000x' as v )t /* V vv y000yy第0001号xx000x y000yy第1号xx000x */
select V ,
case when v like '%第%[0-9]%号%'
then LEFT(v,patindex('%第%[0-9]%号%',v))+
replace(SUBSTRING(v,patindex('%第%[0-9]%号%',v)+1,patindex('%号%',v)),'0','')
else null
end as vv
from
(
select 'y000yy第0001号xx000x' as v
)t
/*
V vv
y000yy第0001号xx000x y000yy第1号xxx
*/
select V ,
case when v like '%第0%号%'
then LEFT(v,patindex('%第0%号%',v))+
substring(v,patindex('%第0%号%',v)-1+
patINDEX('%[1-9]%',substring(v,patindex('%第0%号%',v),LEN(v))),
LEN(v))
else null
end as vv
from
(
select 'y000yy第00101号xx000x' as v
)t
/*
V vv
y000yy第00101号xx000x y000yy第101号xx000x
*/
这个思路不错,这个是第二种方法,通过转化为int,然后再次转化为varchar:
select V ,
case when v like '%第0%号%'
then LEFT(v,patindex('%第0%号%',v))+
cast(cast(SUBSTRING(v,
patindex('%第0%号%',v)+1,
patindex('%号%',v)-patindex('%第%',v)-1)
as int)
as varchar)+
SUBSTRING(v,patindex('%号%',v),LEN(v))
else null
end as vv
from
(
select 'y000yy第0001号xx000x' as v
)t
/*
V vv
y000yy第0001号xx000x y000yy第1号xx000x
*/
这个思路不错,这个是第二种方法,通过转化为int,然后再次转化为varchar:
select V ,
case when v like '%第0%号%'
then LEFT(v,patindex('%第0%号%',v))+
cast(cast(SUBSTRING(v,
patindex('%第0%号%',v)+1,
patindex('%号%',v)-patindex('%第%',v)-1)
as int)
as varchar)+
SUBSTRING(v,patindex('%号%',v),LEN(v))
else null
end as vv
from
(
select 'y000yy第0001号xx000x' as v
)t
/*
V vv
y000yy第0001号xx000x y000yy第1号xx000x
*/
恩恩 谢谢大神