CREATE FUNCTION [dbo].[StringExtractNumber(FirstOrLast)](@address nvarchar(max),@firstOrLast INT)
returns INT
as
BEGIN
DECLARE @returnInt VARCHAR(20)
IF @firstOrLast=1
SET @returnInt= REVERSE(SUBSTRING(
STUFF(reverse(@address),1,PATINDEX('%[0-9]%',reverse(@address))-1,''),
0,
PATINDEX(
'%[^0-9]%',
STUFF(reverse(@address),1,PATINDEX('%[0-9]%',reverse(@address))-1,'')
)
))
ELSE
SET @returnInt= SUBSTRING(
STUFF(@address,1,PATINDEX('%[0-9]%',@address)-1,''),
0,
PATINDEX(
'%[^0-9]%',
STUFF(@address,1,PATINDEX('%[0-9]%',@address)-1,'')
)
)
RETURN CONVERT(INT,@returnInt)
end
--SELECT dbo.[StringExtractNumber(FirstOrLast)]('数据库78个数据库44',0)