--提取数字
IFOBJECT_ID('DBO.GET_NUMBER2') ISNOTNULL
DROPFUNCTION DBO.GET_NUMBER2
GO
CREATEFUNCTION DBO.GET_NUMBER2(@SVARCHAR(100))
RETURNSVARCHAR(100)
AS
BEGIN
WHILEPATINDEX('%[^0-9]%',@S) >0
BEGIN
set@s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
END
RETURN@S
END
GO
--测试
PRINT DBO.GET_NUMBER('呵呵ABC123ABC')
GO
--123
--------------------------------------------------------------------
--提取英文
IFOBJECT_ID('DBO.GET_STR') ISNOTNULL
DROPFUNCTION DBO.GET_STR
GO
CREATEFUNCTION DBO.GET_STR(@SVARCHAR(100))
RETURNSVARCHAR(100)
AS
BEGIN
WHILEPATINDEX('%[^a-z]%',@S) >0
BEGIN
set@s=stuff(@s,patindex('%[^a-z]%',@s),1,'')
END
RETURN@S
END
GO
--测试
PRINT DBO.GET_STR('呵呵ABC123ABC')
GO
--------------------------------------------------------------------
--提取中文
IFOBJECT_ID('DBO.CHINA_STR') ISNOTNULL
DROPFUNCTION DBO.CHINA_STR
GO
CREATEFUNCTION DBO.CHINA_STR(@SNVARCHAR(100))
RETURNSVARCHAR(100)
AS
BEGIN
WHILEPATINDEX('%[^吖-座]%',@S) >0
SET@S=STUFF(@S,PATINDEX('%[^吖-座]%',@S),1,N'')
RETURN@S
END
GO
PRINT DBO.CHINA_STR('呵呵ABC123ABC')
GO
--------------------------------------------------------------------
--过滤重复字符
IFOBJECT_ID('DBO.DISTINCT_STR') ISNOTNULL
DROPFUNCTION DBO.DISTINCT_STR
GO
CREATEFUNCTION DBO.DISTINCT_STR(@SNVARCHAR(100),@SPLITVARCHAR(50))
RETURNSVARCHAR(100)
AS
BEGIN
IF@SISNULLRETURN(NULL)
DECLARE@NEWVARCHAR(50),@INDEXINT,@TEMPVARCHAR(50)
IFLEFT(@S,1)<>@SPLIT
SET@S=@SPLIT+@S
IFRIGHT(@S,1)<>@SPLIT
SET@S=@S+@SPLIT
WHILECHARINDEX(@SPLIT,@S)>0ANDLEN(@S)<>1
BEGIN
SET@INDEX=CHARINDEX(@SPLIT,@S)
SET@TEMP=LEFT(@S,CHARINDEX(@SPLIT,@S,@INDEX+LEN(@SPLIT)))
IF@NEWISNULL
SET@NEW=ISNULL(@NEW,'')+@TEMP
ELSE
SET@NEW=ISNULL(@NEW,'')+REPLACE(@TEMP,@SPLIT,'')+@SPLIT
WHILECHARINDEX(@TEMP,@S)>0
BEGIN
SET@S=STUFF(@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT),CHARINDEX(@SPLIT,@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT))-CHARINDEX(@TEMP,@S),'')
END
END
RETURNRIGHT(LEFT(@NEW,LEN(@NEW)-1),LEN(LEFT(@NEW,LEN(@NEW)-1))-1)
END
GO
PRINT DBO.DISTINCT_STR('A,A,B,C,C,B,C,',',')
--A,B,C
GO
--------------------------------------------------------------------
--过滤重复字符2
IFOBJECT_ID('DBO.DISTINCT_STR2') ISNOTNULL
DROPFUNCTION DBO.DISTINCT_STR2
GO
CREATEFUNCTION DBO.DISTINCT_STR2(@Svarchar(8000))
RETURNSVARCHAR(100)
AS
BEGIN
IF@SISNULLRETURN(NULL)
DECLARE@NEWVARCHAR(50),@INDEXINT,@TEMPVARCHAR(50)
WHILELEN(@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
--------------------------------------------------------------------
IFOBJECT_ID('DBO.SPLIT_STR') ISNOTNULL
DROPFUNCTION DBO.SPLIT_STR
GO
CREATEFUNCTION DBO.SPLIT_STR(
@Svarchar(8000), --包含多个数据项的字符串
@INDEXint, --要获取的数据项的位置
@SPLITvarchar(10) --数据分隔符
)
RETURNSVARCHAR(100)
AS
BEGIN
IF@SISNULLRETURN(NULL)
DECLARE@SPLITLENint
SELECT@SPLITLEN=LEN(@SPLIT+'A')-2
WHILE@INDEX>1ANDCHARINDEX(@SPLIT,@S+@SPLIT)>0
SELECT@INDEX=@INDEX-1,@S=STUFF(@S,1,CHARINDEX(@SPLIT,@S+@SPLIT)+@SPLITLEN,'')
RETURN(ISNULL(LEFT(@S,CHARINDEX(@SPLIT,@S+@SPLIT)-1),''))
END
GO
PRINT DBO.SPLIT_STR('AA|BB|CC',2,'|')
--
GO
IFOBJECT_ID('DBO.GET_NUMBER2') ISNOTNULL
DROPFUNCTION DBO.GET_NUMBER2
GO
CREATEFUNCTION DBO.GET_NUMBER2(@SVARCHAR(100))
RETURNSVARCHAR(100)
AS
BEGIN
WHILEPATINDEX('%[^0-9]%',@S) >0
BEGIN
set@s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
END
RETURN@S
END
GO
--测试
PRINT DBO.GET_NUMBER('呵呵ABC123ABC')
GO
--123
--------------------------------------------------------------------
--提取英文
IFOBJECT_ID('DBO.GET_STR') ISNOTNULL
DROPFUNCTION DBO.GET_STR
GO
CREATEFUNCTION DBO.GET_STR(@SVARCHAR(100))
RETURNSVARCHAR(100)
AS
BEGIN
WHILEPATINDEX('%[^a-z]%',@S) >0
BEGIN
set@s=stuff(@s,patindex('%[^a-z]%',@s),1,'')
END
RETURN@S
END
GO
--测试
PRINT DBO.GET_STR('呵呵ABC123ABC')
GO
--------------------------------------------------------------------
--提取中文
IFOBJECT_ID('DBO.CHINA_STR') ISNOTNULL
DROPFUNCTION DBO.CHINA_STR
GO
CREATEFUNCTION DBO.CHINA_STR(@SNVARCHAR(100))
RETURNSVARCHAR(100)
AS
BEGIN
WHILEPATINDEX('%[^吖-座]%',@S) >0
SET@S=STUFF(@S,PATINDEX('%[^吖-座]%',@S),1,N'')
RETURN@S
END
GO
PRINT DBO.CHINA_STR('呵呵ABC123ABC')
GO
--------------------------------------------------------------------
--过滤重复字符
IFOBJECT_ID('DBO.DISTINCT_STR') ISNOTNULL
DROPFUNCTION DBO.DISTINCT_STR
GO
CREATEFUNCTION DBO.DISTINCT_STR(@SNVARCHAR(100),@SPLITVARCHAR(50))
RETURNSVARCHAR(100)
AS
BEGIN
IF@SISNULLRETURN(NULL)
DECLARE@NEWVARCHAR(50),@INDEXINT,@TEMPVARCHAR(50)
IFLEFT(@S,1)<>@SPLIT
SET@S=@SPLIT+@S
IFRIGHT(@S,1)<>@SPLIT
SET@S=@S+@SPLIT
WHILECHARINDEX(@SPLIT,@S)>0ANDLEN(@S)<>1
BEGIN
SET@INDEX=CHARINDEX(@SPLIT,@S)
SET@TEMP=LEFT(@S,CHARINDEX(@SPLIT,@S,@INDEX+LEN(@SPLIT)))
IF@NEWISNULL
SET@NEW=ISNULL(@NEW,'')+@TEMP
ELSE
SET@NEW=ISNULL(@NEW,'')+REPLACE(@TEMP,@SPLIT,'')+@SPLIT
WHILECHARINDEX(@TEMP,@S)>0
BEGIN
SET@S=STUFF(@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT),CHARINDEX(@SPLIT,@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT))-CHARINDEX(@TEMP,@S),'')
END
END
RETURNRIGHT(LEFT(@NEW,LEN(@NEW)-1),LEN(LEFT(@NEW,LEN(@NEW)-1))-1)
END
GO
PRINT DBO.DISTINCT_STR('A,A,B,C,C,B,C,',',')
--A,B,C
GO
--------------------------------------------------------------------
--过滤重复字符2
IFOBJECT_ID('DBO.DISTINCT_STR2') ISNOTNULL
DROPFUNCTION DBO.DISTINCT_STR2
GO
CREATEFUNCTION DBO.DISTINCT_STR2(@Svarchar(8000))
RETURNSVARCHAR(100)
AS
BEGIN
IF@SISNULLRETURN(NULL)
DECLARE@NEWVARCHAR(50),@INDEXINT,@TEMPVARCHAR(50)
WHILELEN(@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
--------------------------------------------------------------------
IFOBJECT_ID('DBO.SPLIT_STR') ISNOTNULL
DROPFUNCTION DBO.SPLIT_STR
GO
CREATEFUNCTION DBO.SPLIT_STR(
@Svarchar(8000), --包含多个数据项的字符串
@INDEXint, --要获取的数据项的位置
@SPLITvarchar(10) --数据分隔符
)
RETURNSVARCHAR(100)
AS
BEGIN
IF@SISNULLRETURN(NULL)
DECLARE@SPLITLENint
SELECT@SPLITLEN=LEN(@SPLIT+'A')-2
WHILE@INDEX>1ANDCHARINDEX(@SPLIT,@S+@SPLIT)>0
SELECT@INDEX=@INDEX-1,@S=STUFF(@S,1,CHARINDEX(@SPLIT,@S+@SPLIT)+@SPLITLEN,'')
RETURN(ISNULL(LEFT(@S,CHARINDEX(@SPLIT,@S+@SPLIT)-1),''))
END
GO
PRINT DBO.SPLIT_STR('AA|BB|CC',2,'|')
--
GO
