아래와 같이 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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, '_'11) 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
cs


>> 결과 집합


>> 필요 함수

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
/*
    원본 문자열에서 특정 문자열을 제거
*/
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+ 199) ) 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
 
cs


오라클

아래와 같이 구분자로 구분된 여러 항목이 들어 있는 문자열을 파싱하여 표 형태로 출력해 주는 쿼리 입니다.

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'

Row 구분자 :  "_"

Column 구분자 : "/"

1
2
3
4
5
6
7
8
9
10
11
12
13
14
  SELECT TD.RNUM
  , MAX(CASE WHEN TD.LV = 1 THEN TD.TXT ELSE NULL END) COL1
  , MAX(CASE WHEN TD.LV = 2 THEN TD.TXT ELSE NULL END) COL2
  , MAX(CASE WHEN TD.LV = 3 THEN TD.TXT ELSE NULL END) COL3
  , MAX(CASE WHEN TD.LV = 4 THEN TD.TXT ELSE NULL END) COL4
  FROM (
  SELECT TB.RNUM, TC.LV ,regexp_substr(TB.TXT, '[^/]+'1, TC.LV) TXT
  FROM (
  SELECT rownum RNUM,regexp_substr(TA.TXT, '[^_]+'1, LEVEL) TXT
  FROM (SELECT '1/1/W/101_1/2/H/201_1/3/H/301_2/3/H/501_3/4/H/601_4/5/H/701' TXT FROM dual) TA
  CONNECT BY LEVEL <= length(regexp_replace(TA.TXT, '[^_]+',''))+1
  ) TB, (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 4) TC ) TD
  GROUP BY TD.RNUM
  ORDER BY TD.RNUM ASC;
cs

▶ 출력 결과


기타 문의 사항은 덧글로 부탁 드립니다.


+ Recent posts