MS SQL Server文字分割関数

Insus.NETは以前XQueryのnodes()メソッドで1つの関数を書いたことがあります.MS SQL文字分割の関数です.http://www.cnblogs.com/insus/archive/2012/02/26/2368283.html今日SQLを勉強して、もう1つ書いてみます.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        Insus.NET

-- Create date: 2013-06-30

-- Description:    split string function.

-- =============================================

CREATE FUNCTION [dbo].[udf_Split](@value NVARCHAR(MAX), @delimiter CHAR(1))

RETURNS @Results TABLE ([ID] INT IDENTITY(1,1),[WORD] NVARCHAR(MAX))

AS

BEGIN

    DECLARE @index INT

    DECLARE @slice NVARCHAR(MAX)

 

    SET @index = 1

    IF @value IS NULL 

        RETURN

 

    WHILE @index <> 0

    BEGIN

        SET @index = CHARINDEX(@delimiter, @value)

        IF @index !=0

            SET @slice = LEFT(@value,@index - 1)

        ELSE

            SET @slice = @value

 

        INSERT INTO @Results([WORD]) VALUES(@slice)

        SET @value = RIGHT(@value,LEN(@value) - @index)

 

        IF LEN(@value) = 0 BREAK

    END 

    RETURN

END

 

GO

udf_Split
 
例: