텍스트 자르기 함수(테이블 반환)

-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ============================================= 
-- Author:       
-- Create date:

-- Description: SPLIT Function 

-- =============================================  
CREATE FUNCTION [dbo].[UF_TEXT_SPLIT]  
(
     -- Add the parameters for the function here 
    @TEXT       NVARCHAR(100) 
    ,@DELIMITER CHAR(1)  
)
RETURNS @STRINGS TABLE
( 
    POSITION INT IDENTITY(1,1) PRIMARY KEY,  
    VALUE NVARCHAR(MAX) 
) 
AS
BEGIN
    -- Declare the return variable here 
    DECLARE @INDEX  INT
     
    -- Add the T-SQL statements to compute the return value here 
    SET @INDEX = -1 
     
    WHILE (LEN(@text) > 0) 
    BEGIN
        SET @INDEX = CHARINDEX(@DELIMITER , @TEXT) 
         
        IF (@INDEX = 0) AND (LEN(@TEXT) > 0) 
        BEGIN
            INSERT INTO @STRINGS VALUES (@TEXT) 
            BREAK 
        END
         
        IF (@INDEX > 1) 
        BEGIN
            INSERT INTO @STRINGS VALUES (LEFT(@TEXT, @INDEX - 1)) 
            SET @TEXT = RIGHT(@TEXT, (LEN(@TEXT) - @INDEX)) 
        END
        ELSE
            SET @TEXT = RIGHT(@TEXT, (LEN(@TEXT) - @INDEX))  
    END
 
    -- Return the result of the function 
    RETURN
 
END 
GO