数据库教程:Sql Server中常用的6个自定义函数分享

  --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