아래와 같이 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, '_', 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
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) + 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
 
cs


'Database & Data > MSSQL' 카테고리의 다른 글

MSSQL- 복제 관련 조회 쿼리  (0) 2018.10.28
MSSQL 트랜잭션 로그 축소  (0) 2018.08.12
Oracle 의 CONNECT BY LEVEL 을 MSSQL 로 변환  (0) 2018.08.05
MSSQL-연속 출석일 수 조회 쿼리  (0) 2018.07.24
MSSQL - xml 문자열로 DB 저장하기  (1) 2016.09.25

오라클

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

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

▶ 출력 결과


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


'Database & Data > ORACLE' 카테고리의 다른 글

하나의 테이블에서 Merge Into 실행  (0) 2020.03.21
Oracle-xml 문자열을 테이블에 저장하기 입니다.  (2) 2016.09.25

+ Recent posts

티스토리툴바