MSSQL 문자열을 파싱하여 테이블 구조로 변환

아래와 같이 Row 구분자 = "-" , Column 구분자 = "/" 인 문자열을

테이블 형식으로 변환해 보겠습니다.

EX) '1/1/W/101_1/2/H/201_1/3/H/301_2/3/H/501_3/4/H/601_4/5/H/701'

>> Query

DECLARE @V_STRING VARCHAR(3000);
SET @V_STRING = '1/1/W/101_1/2/H/201_1/3/H/301_2/3/H/501_3/4/H/601_4/5/H/701';
 
WITH TA AS
(
    SELECT 1 LV
    UNION ALL
    SELECT LV + 1 LV FROM TA WHERE LV + 1 <= 4
) ,
TB AS
(
    SELECT 1 RNUM, dbo.regexp_substr(@V_STRING, '_', 1, 1) TXT
    UNION ALL
    SELECT RNUM + 1 RNUM, dbo.regexp_substr(@V_STRING , '_', 1, RNUM + 1) TXT
    FROM TB
    WHERE RNUM + 1 <= LEN(dbo.fn_StripCharacters(@V_STRING, '^_+'))+1
)
SELECT TC.RNUM
    , MAX(CASE WHEN TC.LV = 1 THEN TC.TXT ELSE NULL END) COL1
    , MAX(CASE WHEN TC.LV = 2 THEN TC.TXT ELSE NULL END) COL2
    , MAX(CASE WHEN TC.LV = 3 THEN TC.TXT ELSE NULL END) COL3
    , MAX(CASE WHEN TC.LV = 4 THEN TC.TXT ELSE NULL END) COL4
FROM (
    SELECT TB.RNUM, TA.LV, dbo.regexp_substr(TB.TXT, '/', 1, TA.LV) TXT
    FROM TB,TA
) TC
GROUP BY TC.RNUM
ORDER BY TC.RNUM ASC

>> 결과 집합

>> 필요 함수

/*
    원본 문자열에서 특정 문자열을 제거
*/
CREATE FUNCTION [dbo].[fn_StripCharacters]
(
    @String                NVARCHAR(MAX), 
    @MatchExpression    VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    SET @MatchExpression =  '%['+@MatchExpression+']%'
 
    WHILE PatIndex(@MatchExpression, @String) > 0
        SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')
 
    RETURN @String
 
END
GO
 
 
/*
    원본 문자열에서 특정 문자열 부분을 추출
*/
CREATE  FUNCTION [dbo].[REGEXP_SUBSTR] 
(
    @i_str varchar(1000), 
    @i_delimeter varchar(10), 
    @i_position int,
    @i_index int
) RETURNS varchar(2000)
AS BEGIN
 
    DECLARE @o_str varchar(2000); 
    SET @o_str = '';
 
    WITH t AS (
        SELECT 1 as lvl, CONVERT(VARCHAR(2000), @i_str) AS code
        UNION ALL
        SELECT lvl + 1 as lvl
            , CONVERT(VARCHAR(2000), SUBSTRING(code, CHARINDEX(@i_delimeter, code, 1) + 1, 99) ) AS code
        FROM t
        WHERE CHARINDEX(@i_delimeter, code, 1) > 0
    )
 
    SELECT @o_str = CONVERT(VARCHAR(2000), LEFT(code, CHARINDEX(@i_delimeter, code + @i_delimeter, 1) - 1))
    FROM t
    WHERE lvl = @i_index        
 
    RETURN @o_str
 
END
GO