对一个字符串进行提取,获取其中数字部分,方法如下:
IF OBJECT_ID('DBO.GET_NUMBER') IS NOT NULL
DROP FUNCTION dbo.GET_NUMBER;
GO
CREATE FUNCTION dbo.GET_NUMBER (@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%', @S) > 0
BEGIN
SET @S = STUFF(@S, PATINDEX('%[^0-9]%', @S), 1, '');
END;
RETURN @S;
END;
GO
测试语句:
SELECT DBO.GET_NUMBER('12奥奥88ABC')
结果如下: