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
--测试
PRINT DBO.GET_NUMBER("呵呵ABC-12,3ABC")
GO
--123
--------------------------------------------------------------------
--提取英文
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]%",@S) > 0
BEGIN
set @s=stuff(@s,patindex("%[^a-z]%",@s),1,"")
END
RETURN @S
END
GO
--测试
PRINT DBO.GET_STR("呵呵ABC123ABC")
GO
--------------------------------------------------------------------
--提取中文
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),1,N"")
RETURN @S
END
GO
PRINT DBO.CHINA_STR("呵呵ABC123ABC")
GO
--------------------------------------------------------------------
--过滤重复字符
IF OBJECT_ID("DBO.DISTINCT_STR") IS NOT NULL
DROP FUNCTION DBO.DISTINCT_STR
GO
CREATE FUNCTION DBO.DISTINCT_STR(@S NVARCHAR(100),@SPLIT VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
IF @S IS NULL RETURN(NULL)
DECLARE @NEW VARCHAR(50),@INDEX INT,@TEMP VARCHAR(50)
IF LEFT(@S,1)<>@SPLIT
SET @S = @SPLIT+@S
IF RIGHT(@S,1)<>@SPLIT
SET @S = @S+@SPLIT
WHILE CHARINDEX(@SPLIT,@S)>0 AND LEN(@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),CHARINDEX(@SPLIT,@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT))-CHARINDEX(@TEMP,@S),"")
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,C,B,C,",",")
--A,B,C
GO
--------------------------------------------------------------------
--过滤重复字符2
IF OBJECT_ID("DBO.DISTINCT_STR2") IS NOT NULL
DROP FUNCTION DBO.DISTINCT_STR2
GO
CREATE FUNCTION DBO.DISTINCT_STR2(@S varchar(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,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
mssql 端口|mssql 字符串中提取数字，英文，中文，过滤重复字符实例
http://m.bbyears.com/shujuku/32468.html
推荐访问:mssql数据库 mssqlserver mssql2008 mssqlserver服务无法启动 mssql2000 mssql2005 mssql管理工具