select case when CHARINDEX('*',col)>0 then SUBSTRING(col,charindex('熔',COL)+1,CHARINDEX('*',col)-charindex('熔',COL)-1) when CHARINDEX('*',col)=0 and patindex('%[0-9]%',col)>0 then SUBSTRING(col,patindex('%[0-9]%',col),LEN(col)-patindex('%[0-9]%',col)) when CHARINDEX('*',col)=0 and patindex('%[0-9]%',col)=0 then 0
end from tb
Declare @S1 varchar(100) declare @a1 varchar(4) set @S1='仓库退熔888.32' if isnull(substring(@S1,5,4),'')= '' begin set @a1='0' end else begin Set @a1= substring(@S1,5,4) end select @a1
case when CHARINDEX('*',col)>0 then SUBSTRING(col,charindex('熔',COL)+1,CHARINDEX('*',col)-charindex('熔',COL)-1)
when CHARINDEX('*',col)=0 and patindex('%[0-9]%',col)>0 then SUBSTRING(col,patindex('%[0-9]%',col),LEN(col)-patindex('%[0-9]%',col))
when CHARINDEX('*',col)=0 and patindex('%[0-9]%',col)=0 then 0
end
from
tb
declare @a1 varchar(4)
set @S1='仓库退熔888.32'
if isnull(substring(@S1,5,4),'')= ''
begin
set @a1='0'
end
else
begin
Set @a1= substring(@S1,5,4)
end
select @a1