提取数字、英文、中文、过滤重复字符等SQL函数（含判断字段是否有中文）

在日常应用中，往往根据实际需求录入一些值，而这些值不能直接使用，所以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 NULLDROP FUNCTION DBO.GET_NUMBER2GOCREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100))RETURNS VARCHAR(100)ASBEGINWHILE PATINDEX('%[^0-9]%',@S) > 0BEGINset @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')ENDRETURN @SENDGO--测试PRINT DBO.GET_NUMBER('呵呵ABC123ABC')GO--123

三、提取英文

--提取英文IF OBJECT_ID('DBO.GET_STR') IS NOT NULLDROP FUNCTION DBO.GET_STRGOCREATE FUNCTION DBO.GET_STR(@S VARCHAR(100))RETURNS VARCHAR(100)ASBEGINWHILE PATINDEX('%[^a-z]%',@S) > 0BEGINset @s=stuff(@s,patindex('%[^a-z]%',@s),1,'')ENDRETURN @SENDGO--测试PRINT DBO.GET_STR('呵呵ABC123ABC')GO

四、提取中文

--提取中文IF OBJECT_ID('DBO.CHINA_STR') IS NOT NULLDROP FUNCTION DBO.CHINA_STRGOCREATE FUNCTION DBO.CHINA_STR(@S NVARCHAR(100))RETURNS VARCHAR(100)ASBEGINWHILE PATINDEX('%[^吖-座]%',@S) > 0SET @S = STUFF(@S,PATINDEX('%[^吖-座]%',@S),1,N'')RETURN @SENDGOPRINT DBO.CHINA_STR('呵呵ABC123ABC')GO

五、过滤重复字段（多种方法）

---------------------------------------------------------------------过滤重复字符IF OBJECT_ID('DBO.DISTINCT_STR') IS NOT NULLDROP FUNCTION DBO.DISTINCT_STRGOCREATE FUNCTION DBO.DISTINCT_STR(@S NVARCHAR(100),@SPLIT VARCHAR(50))RETURNS VARCHAR(100)ASBEGINIF @S IS NULL RETURN(NULL)DECLARE @NEW VARCHAR(50),@INDEX INT,@TEMP VARCHAR(50)IF LEFT(@S,1)<>@SPLITSET @S = @SPLIT+@SIF RIGHT(@S,1)<>@SPLITSET @S = @S+@SPLITWHILE CHARINDEX(@SPLIT,@S)>0 AND LEN(@S)<>1BEGINSET @INDEX = CHARINDEX(@SPLIT,@S)SET @TEMP = LEFT(@S,CHARINDEX(@SPLIT,@S,@INDEX+LEN(@SPLIT)))IF @NEW IS NULL SET @NEW = ISNULL(@NEW,'')+@TEMPELSESET @NEW = ISNULL(@NEW,'')+REPLACE(@TEMP,@SPLIT,'')+@SPLITWHILE CHARINDEX(@TEMP,@S)>0BEGINSET @S=STUFF(@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT),CHARINDEX(@SPLIT,@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT))-CHARINDEX(@TEMP,@S),'')ENDENDRETURN RIGHT(LEFT(@NEW,LEN(@NEW)-1),LEN(LEFT(@NEW,LEN(@NEW)-1))-1)ENDGOPRINT DBO.DISTINCT_STR('A,A,B,C,C,B,C,',',')--A,B,CGO----------------------------------------------------------------------过滤重复字符2IF OBJECT_ID('DBO.DISTINCT_STR2') IS NOT NULLDROP FUNCTION DBO.DISTINCT_STR2GOCREATE FUNCTION DBO.DISTINCT_STR2(@S varchar(8000))RETURNS VARCHAR(100)ASBEGINIF @S IS NULL RETURN(NULL)DECLARE @NEW VARCHAR(50),@INDEX INT,@TEMP VARCHAR(50)WHILE LEN(@S)>0BEGINSET @NEW=ISNULL(@NEW,'')+LEFT(@S,1)SET @S=REPLACE(@S,LEFT(@S,1),'')ENDRETURN @NEWENDGOSELECT DBO.DISTINCT_STR2('AABCCD')--ABCDGO

六、根据特定字符串分割字段值

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

 提取中文与英文与数字

USE [THAccountsDB]GO/****** Object:  UserDefinedFunction [dbo].[getNormalStr]    Script Date: 01/07/2017 11:03:16 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER  function  [dbo].[getNormalStr](@str  nvarchar(4000))    returns  nvarchar(4000)      as      begin      declare  @word  nchar(1)  declare @CN  nvarchar(4000)      set  @CN=''      while  len(@str)>0      begin      set  @word=left(@str,1)    --print @word    if unicode(@word)  between  19968  and  19968+20901  --中文or unicode(@word) between 48 and 48+10--数字or UNICODE(@word) between 65 and 65+26--大写英文or UNICODE(@word) between 97 and 97+26--小写英文  set  @CN=@CN+@word  set  @str=right(@str,len(@str)-1)      end        return  @CN      end  

原文 http://www.cnblogs.com/dyjerry/p/4044928.html