MS SQL Server字符拆分函数

Insus.NET以前实惠XQuery的nodes()方法勾勒过一个函数,是MS
SQL字符拆分的函数,http://www.cnblogs.com/insus/archive/2012/02/26/2368283.html

今习SQL,再尝试任何写一个:

图片 1图片 2

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

 

事例演示:
图片 3

相关文章