sql 字符取数字

update newpllccn_jiangjingfenlei set classid=
left(SUBSTRING(url,PATINDEX('%[0-9]%',url),LEN(url)),patindex('%[^0-9]%',SUBSTRING(url,PATINDEX('%[0-9]%',url),LEN(url))+'a')-1)

select SUBSTRING(url,PATINDEX('%[0-9]%',url),LEN(url)) a from newpllccn_jiangjingfenlei 

select left(SUBSTRING(url,PATINDEX('%[0-9]%',url),LEN(url)),patindex('%[^0-9]%',SUBSTRING(url,PATINDEX('%[0-9]%',url),LEN(url))+'a')-1) a from newpllccn_jiangjingfenlei 

 

 

参考：

 

declare @var nvarchar(max)='Balance1000sheet'

SELECT LEFT(Val,PATINDEX('%[^0-9]%', Val+'a')-1) from(
    SELECT SUBSTRING(@var, PATINDEX('%[0-9]%', @var), LEN(@var)) Val
)x