SQL Server 提取数字、提取英文、提取中文的sql语句

--SQL 判断字段值是否有中文
create function fun_getCN(@str nvarchar(4000))  
returns nvarchar(4000)  
 as  
 begin  
 declare @word nchar(1),@CN nvarchar(4000)  
 set @CN=  
 while len(@str)>0  
 begin  
 set @word=left(@str,1)  
 if unicode(@word) between 19968 and 19968+20901 
   set @CN=@CN+@word
 set @str=right(@str,len(@str)-1)  
 end  
 return @CN  
 end  
select dbo.fun_getCN(ASDKG论坛KDL)
--论坛
select dbo.fun_getCN(ASDKG論壇KDL)
--論壇
select dbo.fun_getCN(ASDKDL)
--空

-------------------------------------
--提取数字
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(呵呵ABC123ABC)
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