SQL字符串分割解析

　　常用以下三种：

　　【1】substring( expression ,start , length )；

　　【2】CHARINDEX ( expression1 , expression2 [ , start_location ] ) ；

　　【3】LEFT(expression,start)；

　　注意：

　　(1)substring中：对于start，负数和0都是空，真正有意义的位置是从" 1 "开始。

　　(2)CHARINDEX中：expression1是要到expression2中寻找的字符，start_location是CHARINDEX函数开始在expression2中找expression1的位置。

　　(3)LEFT中：表示截取expression的start索引位置左侧的字符串（包括索引位置在内）。

 

　　方法一：动态SQL法

DECLARE @s VARCHAR(50),@sql VARCHAR(300)
SET @s='1,2,3,4,5,6,7,8,9,10'
SET @sql='SELECT col='''+ REPLACE(@s,',',''' UNION ALL SELECT ''')+''''
PRINT @sql
EXEC (@sql)

　　方法二：循环截取法

IF EXISTS (
    SELECT * FROM dbo.sysobjects
        WHERE id = OBJECT_ID(N'[dbo].[f_splitSTR]') AND xtype IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION [dbo].[f_splitSTR];
GO

CREATE FUNCTION f_splitSTR(  
    @s VARCHAR(8000),   --待分拆的字符串  
    @split VARCHAR(10)    --数据分隔符  
)RETURNS @re TABLE(col VARCHAR(100))  
AS  
BEGIN  
    WHILE CHARINDEX(@split,@s)>0  
    BEGIN  
        INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
        --删除目标字符串从start开始length并插入指定字符串
        SET @s=STUFF(@s,1,CHARINDEX(@split,@s),'') 
        --Set @s = Substring(@s, CharIndex(@split,@s)+@splitle, 100) 
    END  
    INSERT @re VALUES(@s)  
    RETURN  
END
GO

　　方法三：使用临时性分拆辅助表法

IF EXISTS (
    SELECT * FROM dbo.sysobjects
        WHERE id = OBJECT_ID(N'[dbo].[f_splitSTR]') AND xtype IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION [dbo].[f_splitSTR];
GO

CREATE FUNCTION f_splitSTR(
    @s VARCHAR(8000),  --待分拆的字符串
    @split VARCHAR(10) --数据分隔符
)RETURNS @re TABLE(col VARCHAR(100))
AS
BEGIN
    --创建分拆处理的辅助表(用户定义函数中只能操作表变量)  
    DECLARE @t TABLE(ID INT IDENTITY,b BIT)
    INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns va
    INSERT @re SELECT SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID)
           FROM @t WHERE ID<=LEN(@s) AND CHARINDEX(@split,@s+@split,ID)=ID
    RETURN
END
GO

　　方法四：使用永久性分拆辅助表法

IF EXISTS (
    SELECT * FROM dbo.sysobjects
        WHERE id = OBJECT_ID(N'[dbo].[f_splitSTR]') AND xtype IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION [dbo].[f_splitSTR];
GO
IF EXISTS (
    SELECT * FROM dbo.sysobjects 
        WHERE id = OBJECT_ID(N'[dbo].[tb_splitSTR]') and objectproperty(id,N'IsUserTable')=1
)  
DROP TABLE [dbo].[tb_splitSTR]
GO 

--字符串分拆辅助表
SELECT TOP 8000 ID=IDENTITY(int,1,1) INTO dbo.tb_splitSTR FROM syscolumns a
GO
--字符串分拆处理函数
CREATE FUNCTION f_splitSTR(
    @s varchar(8000),  --待分拆的字符串
    @split varchar(10)     --数据分隔符
)RETURNS TABLE
AS
RETURN(
 SELECT col=CAST(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID) as varchar(100))
   FROM tb_splitSTR
   WHERE ID<=LEN(@s) AND CHARINDEX(@split,@s+@split,ID)=ID)
GO

　　方法五：利用sql server2005的OUTER APPLY

IF EXISTS (
    SELECT * FROM dbo.sysobjects
        WHERE id = OBJECT_ID(N'[dbo].[f_splitSTR]') AND xtype IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION [dbo].[f_splitSTR];
GO

CREATE FUNCTION [dbo].[f_splitSTR]
(
  @str VARCHAR(MAX) ,
  @split VARCHAR(10)
)
RETURNS TABLE
AS
RETURN
    ( SELECT    B.id
      FROM      ( SELECT    [value] = CONVERT(XML , '<v>' + REPLACE(@str , @split , '</v><v>')
                            + '</v>')
                ) A
      OUTER APPLY ( SELECT  id = N.v.value('.' , 'varchar(100)')
                    FROM    A.[value].nodes('/v') N ( v )
                  ) B
    )