SQL Server 拆分字符串函数

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(‘func_get_split_string_total’) AND type in (N’FN’, N’IF’, N’TF’, N’FS’, N’FT’))
DROP FUNCTION func_get_split_string_total
GO

/****** Object: UserDefinedFunction [dbo].[func_get_split_string_total] Script Date: 06/26/2015 08:35:02 *****/
/

• 将字符串拆分成 列 如： ‘1,2,3’ 可将 ‘1’,‘2’,'3’插入到 @temp 的 code 列中
• 用法： select code from f_SplitToTable(‘1,2,3’,’,’)
• 返回 字符的总数
  */
  create function func_get_split_string_total(
  @SourceSql varchar(8000) – 要拆分的字符串
  ,@StrSeprate varchar(100) – 要分隔字符串的字符 如 逗号 “,”
  )
  returns int
  as
  begin
  declare @ch as varchar(100) ;
  declare @v_str_num int;
  declare @temp table(code varchar(100)) ;
  set @v_str_num = 0 ;
  if @SourceSql is null or @SourceSql = ‘’
  begin
  set @SourceSql = @StrSeprate;
  set @v_str_num = 0;
  end
  else
  begin
  set @SourceSql=@SourceSql+@StrSeprate ;
  while(@SourceSql<>’’)
  begin
  set @ch=left(@SourceSql,charindex(@StrSeprate,@SourceSql,1)-1)
  insert @temp values(@ch)
  set @SourceSql=stuff(@SourceSql,1,charindex(@StrSeprate,@SourceSql,1),’’)
  end
  select @v_str_num = COUNT(1) from @temp ;
  end;
  return @v_str_num
  end
  go