在sqlserver中如何从字符串中提取数字，英文，中文，过滤重复字

在sqlserver中如何从字符串中提取数字，英文，中文，过滤重复字符

?

-提取数字
IF?OBJECT_ID('DBO.GET_NUMBER2')?IS?NOT?NULL
DROP?FUNCTION?DBO.GET_NUMBER2
GO
CREATE?FUNCTION?DBO.GET_NUMBER2(@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
--测试
PRINT?DBO.GET_NUMBER('2012中国China1949bj')
GO
--20121949

--------------------------------------------------------------------
--提取英文
IF?OBJECT_ID('DBO.GET_STR')?IS?NOT?NULL
DROP?FUNCTION?DBO.GET_STR
GO
CREATE?FUNCTION?DBO.GET_STR(@S?VARCHAR(100))
RETURNS?VARCHAR(100)
AS
BEGIN
WHILE?PATINDEX('%[^a-z]%',patindex('%[^a-z]%','')
END
RETURN?@S
END
GO
--测试
PRINT?DBO.GET_STR('2012中国China1949bj')
GO

--Chinabj
--------------------------------------------------------------------
--提取中文
IF?OBJECT_ID('DBO.CHINA_STR')?IS?NOT?NULL
DROP?FUNCTION?DBO.CHINA_STR
GO
CREATE?FUNCTION?DBO.CHINA_STR(@S?NVARCHAR(100))
RETURNS?VARCHAR(100)
AS
BEGIN
WHILE?PATINDEX('%[^吖-座]%',@S)?>?0
SET?@S?=?STUFF(@S,PATINDEX('%[^吖-座]%',@S),N'')
RETURN?@S
END
GO
PRINT?DBO.CHINA_STR('2012中国China1949bj')
GO
--中国

?

--------------------------------------------------------------------
--过滤重复字符
IF?OBJECT_ID('DBO.DISTINCT_STR')?IS?NOT?NULL
DROP?FUNCTION?DBO.DISTINCT_STR
GO
CREATE?FUNCTION?DBO.DISTINCT_STR(@SNVARCHAR(100),@SPLITVARCHAR(50))
RETURNS?VARCHAR(100)
AS
BEGIN
IF?@S?IS?NULL?RETURN(NULL)
DECLARE@NEWVARCHAR(50),@INDEXINT,@TEMPVARCHAR(50)
IF?LEFT(@S,1)<>@SPLIT
SET?@S=@SPLIT+@S
IF?RIGHT(@S,1)<>@SPLIT
SET?@S=@S+@SPLIT
WHILE?CHARINDEX(@SPLIT,@S)>0ANDLEN(@S)<>1
BEGIN
SET?@INDEX=CHARINDEX(@SPLIT,@S)
SET@TEMP=LEFT(@S,CHARINDEX(@SPLIT,@S,@INDEX+LEN(@SPLIT)))
IF?@NEW?IS?NULL
SET@NEW=ISNULL(@NEW,'')+@TEMP
ELSE
SET?@NEW=ISNULL(@NEW,'')+REPLACE(@TEMP,@SPLIT,'')+@SPLIT
WHILE?CHARINDEX(@TEMP,@S)>0
BEGIN
SET?@S=STUFF(@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT),@S)+LEN(@SPLIT))-CHARINDEX(@TEMP,'')
END
END
RETURN?RIGHT(LEFT(@NEW,LEN(@NEW)-1),LEN(LEFT(@NEW,LEN(@NEW)-1))-1)
END
GO
PRINT?DBO.DISTINCT_STR('A,A,B,C,',')
--A,C
GO

?

--------------------------------------------------------------------
--过滤重复字符2
IF?OBJECT_ID('DBO.DISTINCT_STR2')?IS?NOT?NULL
DROP?FUNCTION?DBO.DISTINCT_STR2
GO
CREATE?FUNCTION?DBO.DISTINCT_STR2(@Svarchar(8000))
RETURNS?VARCHAR(100)
AS
BEGIN
IF?@S?IS?NULL?RETURN(NULL)
DECLARE?@NEW?VARCHAR(50),@INDEX?INT,@TEMP?VARCHAR(50)
WHILE?LEN(@S)>0
BEGIN
SET@NEW=ISNULL(@NEW,'')+LEFT(@S,1)
SET@S=REPLACE(@S,LEFT(@S,1),'')
END
RETURN?@NEW
END
GO
SELECT?DBO.DISTINCT_STR2('AABCCD')
--ABCD
GO

-------------------------------------------------------------------- IF?OBJECT_ID('DBO.SPLIT_STR')?IS?NOT?NULL DROP?FUNCTION?DBO.SPLIT_STR GO CREATE?FUNCTION?DBO.SPLIT_STR( @S?varchar(8000),?--包含多个数据项的字符串 @INDEX?int,?--要获取的数据项的位置 @SPLIT?varchar(10)?--数据分隔符 ) RETURNS?VARCHAR(100) AS BEGIN IF?@S?IS?NULL?RETURN(NULL) DECLARE?@SPLITLEN?int SELECT?@SPLITLEN=LEN(@SPLIT+'A')-2 WHILE?@INDEX>1?AND?CHARINDEX(@SPLIT,@S+@SPLIT)>0 SELECT?@INDEX=@INDEX-1,@S=STUFF(@S,@S+@SPLIT)+@SPLITLEN,'') RETURN(ISNULL(LEFT(@S,@S+@SPLIT)-1),'')) END GO PRINT?DBO.SPLIT_STR('AA|BB|CC',2,'|') -- GO

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!