SQL 本身的Replace方式不支持正则表达式方式匹配被替换的字符,因此要想实现就要自己编写一个Function,如去掉字符串中所有非数字和字母的字符
可以使用%[^0-9a-Z]%
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('fn_RegExReplace') IS NOT NULL DROP FUNCTION fn_RegExReplace
GO
-- =============================================
-- Author: Nolan Shang
-- Create date: 05/05/2016
-- Description: Replace string with regex pattern
-- =============================================
CREATE FUNCTION fn_RegExReplace
(
@string VARCHAR(MAX),
@substring VARCHAR(MAX),
@replacement VARCHAR(MAX)
)
RETURNS VARCHAR(max)
AS
BEGIN
DECLARE @sRet VARCHAR(MAX)
DECLARE @iPos INT=1
WHILE @iPos>0
BEGIN
SET @iPos=PATINDEX(@substring,@string)
IF @iPos>0
BEGIN
SET @sRet=isnull(@sRet,'')+left(@string,@iPos-1)+@replacement
SET @string=substring(@string,@iPos+1,CASE WHEN len(@string)<=@iPos THEN LEN(@string) ELSE len(@string)-@iPos END)
END
ELSE
BEGIN
SET @iPos=0
SET @sRet=isnull(@sRet,'')+@string
END
END
RETURN @sRet
END
GO
测试:
SELECT dbo.fn_RegExReplace('~!@#$他%^&*|()_+;''ssdfsf123123,.','%[^0-9a-Z]%','')
结果为:ssdfsf123123