T-SQL: 文字列を指定文字で分離する

SAMPLE

REFERENCE

Split a string to a table using T-SQL

CREATE FUNCTION

CREATE FUNCTION [dbo].[Split]
(
    @string NVARCHAR(2000),
    @delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH SPLIT(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@delimiter,@string) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@delimiter,@string,endpos+1)
            FROM SPLIT
            WHERE endpos > 0
    )
    SELECT 'data' = SUBSTRING(@string,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM SPLIT
)

HOW TO USE

DECLARE @text NVARCHAR(100) = 'XXXXX/YYYYY/WWWWW'
SELECT data
FROM dbo.SPLIT(@text, '/')