第一种方法：
Create Function USF_ExtractNumeric
(
@inputStr nvarchar(50)
)
Returns nvarchar(50)
AS
Begin
Declare @outputStr nvarchar(50)
Set @outputStr = ''
If ISNUMERIC(@inputStr) = 1
Begin
Return @inputStr
End

Declare @I Int
Set @I = 1
While @I <= Len(@inputStr)
Begin
If ASCII(SUBSTRING(@inputStr,@I,1)) <= 57 and ASCII(SUBSTRING(@inputStr,@I,1)) >= 48
Begin
Set @outputStr = @outputStr + SUBSTRING(@inputStr,@I,1)
End
Set @I = @I + 1
End
Return @outputStr
End

 

select dbo.USF_ExtractNumeric('136-0101-1001 张scan')

 

第二种方法：
select substring(RP_UNIT,patindex('%[^0-9][0-9]%',RP_UNIT)+1,patindex('%[0-9][^0-9]%',RP_UNIT)-patindex('%[^0-9][0-9]%',RP_UNIT)) from TB_SalesPriceForCarInfo