-- 判断字段值是否有中文  create function fun_getCN(@str nvarchar(4000))    returns nvarchar(4000)     as     begin     declare @ nchar(1),@CN nvarchar(4000)     set @CN=''     while len(@str)&gt;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('ASDKGKDL')  --  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

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注