Oracle/SQL2012. 4. 13. 14:10

๊ตฌ๋ถ„์ž ํฌํ•จ ๊ฐ’์„ ํ–‰์œผ๋กœ ๋ถ„๋ฆฌ

๊ตฌ๋ถ„์ž ํฌํ•จ ๊ฐ’์„ ํ–‰์œผ๋กœ ๋ถ„๋ฆฌํ•ด๋ณด์ž.


10.1 ์ด์ „ ๋ฒ„์ „๊นŒ์ง€๋Š” ์•„๋ž˜์™€ ๊ฐ™์ด SUBSTR ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•ด์•ผ ํ•œ๋‹ค.

-- 1
WITH w1 AS (SELECT 'A,BB,CCC,DDDD,EEEEE' AS c1 FROM DUAL)
SELECT     c1,
           SUBSTR (c1_n
                 , INSTR (c1_n, ',', 1, LEVEL) + 1
                 , INSTR (c1_n, ',', 1, LEVEL  + 1) - INSTR (c1_n, ',', 1, LEVEL) - 1) AS c1_n
      FROM (SELECT c1, ',' || c1 || ',' AS c1_n
              FROM w1)
CONNECT BY LEVEL <= LENGTH (c1_n) - LENGTH (REPLACE (c1_n, ',')) - 1;

C1                  C1_N
------------------- -----
A,BB,CCC,DDDD,EEEEE A
A,BB,CCC,DDDD,EEEEE BB
A,BB,CCC,DDDD,EEEEE CCC
A,BB,CCC,DDDD,EEEEE DDDD
A,BB,CCC,DDDD,EEEEE EEEEE

5 ํ–‰์ด ์„ ํƒ๋˜์—ˆ์Šต๋‹ˆ๋‹ค.


11.1 ๋ฒ„์ „๋ถ€ํ„ฐ ์•„๋ž˜์™€ ๊ฐ™์ด ์ •๊ทœ ํ‘œํ˜„์‹ ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ๋‹ค.

WITH w1 AS (SELECT 'A,BB,CCC,DDDD,EEEEE' AS c1 FROM DUAL)
SELECT     c1,
           REGEXP_SUBSTR (c1, '[^,]+', 1, LEVEL) AS c1_n
      FROM w1
CONNECT BY LEVEL <= REGEXP_COUNT (c1, ',') + 1;

C1                  C1_N
------------------- -----
A,BB,CCC,DDDD,EEEEE A
A,BB,CCC,DDDD,EEEEE BB
A,BB,CCC,DDDD,EEEEE CCC
A,BB,CCC,DDDD,EEEEE DDDD
A,BB,CCC,DDDD,EEEEE EEEEE

5 ํ–‰์ด ์„ ํƒ๋˜์—ˆ์Šต๋‹ˆ๋‹ค.


๋ฌธ์ž์—ด์— ํฌํ•จ๋œ ๊ตฌ๋ถ„์ž์˜ ๊ฐœ์ˆ˜๋Š” ์•„๋ž˜์˜ ๋ฐฉ์‹์œผ๋กœ ๊ณ„์‚ฐํ•  ์ˆ˜ ์žˆ๋‹ค.

WITH w1 AS (SELECT 'A,BB,CCC,DDDD,EEEEE' AS c1 FROM DUAL)
SELECT c1
     , LENGTH (c1) - LENGTH (REPLACE (c1, ',')) AS c2
     , LENGTH (TRANSLATE (c1, ',' || c1, ',')) AS c3
     , REGEXP_COUNT (c1, ',') AS c4
  FROM w1;

C1                  C2 C3 C4
------------------- -- -- --
A,BB,CCC,DDDD,EEEEE  4  4  4

1๊ฐœ์˜ ํ–‰์ด ์„ ํƒ๋˜์—ˆ์Šต๋‹ˆ๋‹ค.


'Oracle > SQL' ์นดํ…Œ๊ณ ๋ฆฌ์˜ ๋‹ค๋ฅธ ๊ธ€

์ฃผ๋ฏผ๋“ฑ๋ก๋ฒˆํ˜ธ ๋งˆ์Šคํ‚น  (0) 2012.04.30
๋‹ค์ค‘ ํ–‰์„ ๋ฌธ์ž์—ด๋กœ ์ง‘๊ณ„  (0) 2012.04.16
์—ฐ์†๋œ ๊ณต๋ฐฑ์„ ํ•˜๋‚˜์˜ ๊ณต๋ฐฑ์œผ๋กœ ๋ณ€๊ฒฝ  (0) 2012.04.12
์—ฐ์† ์ƒ์Šน ๊ตฌ๊ฐ„ ์กฐํšŒ  (0) 2012.04.10
์  ์ด๋ ฅ์„ ์„ ๋ถ„ ์ด๋ ฅ์œผ๋กœ ์ „ํ™˜  (0) 2012.04.09
Posted by ์ •ํฌ๋ฝ_

ํ‹ฐ์Šคํ† ๋ฆฌํˆด๋ฐ”