tsql 用户函数- 从字符串中提取需要的字符(中文，数字，字母等)

 

ALTER function dbo.ufn_GetNumeric (  
@oldstr varchar(100)  
) returns INT
AS  
/******************************  
函数功能: 从字符串中取数字.  
创建日期: 2007-10-19  
******************************/  
begin  
  declare @s varchar(1000),@i int  
  set @i = 1  
  while @i <= DATALENGTH(@oldstr)  
 BEGIN  
 IF substring(@oldstr collate Chinese_PRC_BIN,@i, 1) like('[0-9]') --取数字  
 SET @s=isnull(@s,'')+substring(@oldstr ,@i,1)  
    set @i = @i +1   
 END  
  RETURN CAST(@s AS INT)  
end
GO

/**//*======================================================*/
alter function dbo.ufn_GetLowerChar(
@oldstr varchar(100)
) returns varchar(100)
AS
/**//******************************
函数功能:    从字符串中取小写字母.
创建日期:    2007-10-26
******************************/
begin
  declare @s varchar(1000),@i int
  set @i = 1
  while @i <= DATALENGTH(@oldstr)
    BEGIN
    if substring(@oldstr collate Chinese_PRC_BIN,@i, 1) like('[a-z]') --取小写字母
    set @s=isnull(@s,'')+substring(@oldstr ,@i,1)
    set @i = @i +1    
    END
  return @s
end
GO

/**//*======================================================*/
alter function dbo.ufn_GetUpperChar(
@oldstr varchar(100)
) returns varchar(100)
AS
/**//******************************
函数功能:    从字符串中取大写字母.
创建日期:    2007-10-26
******************************/
begin
  declare @s varchar(1000),@i int
  set @i = 1
  while @i <= DATALENGTH(@oldstr)
    BEGIN
    if substring(@oldstr collate Chinese_PRC_BIN,@i, 1) like('[A-Z]') --取大写字母
    set @s=isnull(@s,'')+substring(@oldstr ,@i,1)
    set @i = @i +1    
    END
  return @s
end
GO

/**//*======================================================*/
alter function dbo.ufn_GetChineseChar(
@oldstr varchar(100)
) returns varchar(100)
AS
/**//******************************
函数功能:    从字符串中取中文字符.
创建日期:    2007-10-26
******************************/
BEGIN
    DECLARE @s VARCHAR(1000)
    while patindex('%[吖-座]%',@oldstr)>0    
      begin
        SELECT @s=ISNULL(@s,'')+SUBSTRING(@oldstr,PATINDEX('%[吖-座]%',@oldstr),1)
        SET @oldstr=stuff(@oldstr,1,patindex('%[吖-座]%',@oldstr),'')
      end
     return @s
END 
GO 

/**//*测试*/
declare @s varchar(100)
set @s='CHINA,r~!@#$o%likjLKSkF^&*(y,&.;)(*wu^x%943中8国$@人ss'
select dbo.ufn_GetChineseChar(@s)[#1],@s [#2] UNION ALL 
select dbo.ufn_GetUpperChar(@s)[#1],@s [#2] UNION ALL 
select dbo.ufn_GetLowerChar(@s)[#1],@s [#2] UNION ALL 
select dbo.ufn_GetNumeric(@s)[#1],@s [#2]

/**//*
#1                #2
中国人            CHINA,r~!@#$o%likjLKSkF^&*(y,&.;)(*wu^x%943中8国$@人ss
CHINALKSF        CHINA,r~!@#$o%likjLKSkF^&*(y,&.;)(*wu^x%943中8国$@人ss
rolikjkywuxss    CHINA,r~!@#$o%likjLKSkF^&*(y,&.;)(*wu^x%943中8国$@人ss
9438            CHINA,r~!@#$o%likjLKSkF^&*(y,&.;)(*wu^x%943中8国$@人ss

(4 行受影响)
*/