sql中拆分字符串的N中方法和手段

不多说话，看代码：

declare @s as nvarchar(100) --全路径
declare @s1 as nvarchar(50) --总公司
declare @s2 as nvarchar(50) --分公司全路径
declare @s3 as nvarchar(50) --直接上级组织
declare @s4 as nvarchar(50) --分公司
select @s = '中国电信广东公司_本部职能部门_企业信息化部'

if charindex('_市分公司',@s)>0
begin
  set @s=replace(@s,'_市分公司','')
end
if charindex('_',@s) > 0
begin
   set @s1 = left(@s, charindex('_',@s) - 1)
   set @s2 = substring(@s , charindex('_',@s) + 1 , len(@s))
   set @s3=reverse(left(reverse(@s),charindex('_',reverse(@s))-1))
   set @s4=left(@s2, charindex('_',@s2) - 1) 
end

select @s as '全路径',@s1 as '总公司',@s2 as '分公司全路径',@s3 as '用户直接组织',@s4 as '用户分公司或部门'



-- 7998 bytes max (but faster)
   CREATE FUNCTION dbo.udf_Split (@param varchar(7998)) RETURNS TABLE AS
   RETURN(SELECT substring(',' + @param + ',', Number + 1,
                    charindex(',', ',' + @param + ',', Number + 1) - Number - 1)
                 AS Value
          FROM   Numbers
          WHERE  Number <= len(',' + @param + ',') - 1
            AND  substring(',' + @param + ',', Number, 1) = ',')
 
 
-- Unlimited size
   CREATE FUNCTION dbo.udf_SplitUnlim(@list  ntext,
                                     @delim nchar(1) = N',')
   RETURNS @t TABLE (str varchar(4000),
                     nstr nvarchar(2000)) AS
   BEGIN
      DECLARE @slices TABLE (slice nvarchar(4000) NOT NULL)
      DECLARE @slice nvarchar(4000),
              @textpos int,
              @maxlen int,
              @stoppos int
 
      SELECT @textpos = 1, @maxlen = 4000 - 2
      WHILE datalength(@list) / 2 - (@textpos - 1) >= @maxlen
      BEGIN
         SELECT @slice = substring(@list, @textpos, @maxlen)
         SELECT @stoppos = @maxlen - charindex(@delim, reverse(@slice))
         INSERT @slices (slice) VALUES (@delim + left(@slice, @stoppos) + @delim)
         SELECT @textpos = @textpos - 1 + @stoppos + 2   -- On the other side of the comma.
      END
      INSERT @slices (slice)
          VALUES (@delim + substring(@list, @textpos, @maxlen) + @delim)
 
      INSERT @t (str, nstr)
         SELECT str, str
         FROM   (SELECT str = ltrim(rtrim(substring(s.slice, N.Number + 1,
                        charindex(@delim, s.slice, N.Number + 1) - N.Number - 1)))
                 FROM  Numbers N
                 JOIN  @slices s ON N.Number <= len(s.slice) - 1
                                AND substring(s.slice, N.Number, 1) = @delim) AS x
 
      RETURN
   END


--拆分sql 带固定的分割符号的函数
ALTER FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )
GO



-- Create temp table to test inserting values into
create table #t (num int)

-- Create a comma delimited string to test with
declare @str    varchar(500)
select @str = '4,2,7,7834,45,24,45,77'
--------------------------------------------------------
---- Code to load the delimited string into a table ----
--------------------------------------------------------

-- Create insert for comma delimited values
declare @sql varchar(8000)
select @sql = 'insert into #t select '+
    replace(@str,',',' union all select ')

-- Load values from comma delimited string into a table
exec ( @SQL )

--------------------------------------------------------
--------------------------------------------------------

-- Select values from temp table to show results
select * from #t

if object_id('tempdb..#t') is not null
    begin drop table #t end