sqlserver Split 开放写法有兴趣的学习一下

USE [MssDb]
GO
/****** Object:  UserDefinedFunction [dbo].[F_SQLSERVER_SPLIT]    Script Date: 07/19/2016 09:56:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		yanghui
-- ALTER date: 2012-5-18
-- Description:	<split功能>
-- =============================================
ALTER FUNCTION [dbo].[F_SQLSERVER_SPLIT](@Long_str NVARCHAR(MAX),@split_str NVARCHAR(100))    
RETURNS  @tmp TABLE(        
    ID          inT     IDENTITY PRIMARY KEY,      
    short_str   NVARCHAR(MAX)    
)    
AS   
BEGIN   
    DECLARE @short_str NVARCHAR(MAX),@split_str_length int,@split_str_Position_Begin int
    SET @split_str_length = LEN(@split_str) 
    SET @Long_str=REPLACE(REPLACE(@Long_str,CHAR(10),''),CHAR(13),'')
    IF CHARINDEX(@split_str,@Long_str)=1 
         SET @Long_str=STUFF(@Long_str,1,@split_str_length,'')
    IF CHARINDEX(@split_str,@Long_str)=0
        INSERT INTO @tmp SELECT @Long_str 
    ELSE
        BEGIN
            WHILE 1>0    
                BEGIN   
                    SET @split_str_Position_Begin = CHARINDEX(@split_str,@Long_str)
                    SET @short_str=LEFT(@Long_str,@split_str_Position_Begin-1) 
                    IF @short_str<>'' INSERT INTO @tmp SELECT @short_str  
                    SET @Long_str=STUFF(@Long_str,1,@split_str_Position_Begin+@split_str_length-1,'')
                    SET @split_str_Position_Begin = CHARINDEX(@split_str,@Long_str)
                    IF @split_str_Position_Begin=0 
                    BEGIN
                        IF LTRIM(@Long_str)<>''
                            INSERT INTO @tmp SELECT @Long_str 
                        BREAK
                    END
                END           
        END
    RETURN     
END

另外关于sqlserver 合并与拆分我在多说几句，大多用到Split 函数的时候基本需要拆分处理，以上是我的写法一下是大神级别做法，供参考

原始数据如图所示

SELECT *
FROM  dbo.aaa AS t01 CROSS apply dbo.Split(t01.name,',') AS t02

上面是采用split语句拆分处理，拆分之后数据显示如下

合并语句

SELECT 
    id
   ,stuff((
        SELECT 
            ','+CLASS 
        FROM dbo.bbb AS tb01 where tb01.id=tb02.id
        FOR xml PATH('')),1,1,'') AS classList 
 FROM  dbo.bbb AS tb02 
 GROUP BY id

合并之后处理结果如下