2022.07.26 - SQL(2)

sofiaยท2022๋…„ 7์›” 26์ผ
sql
0

SQL

๋ชฉ๋ก ๋ณด๊ธฐ
5/13
post-thumbnail

๐Ÿ“™CASE WHEN ~ THEN ~ (ELSE) END ์‹คํ–‰๋ฌธ

CASE 
					WHEN ์กฐ๊ฑด์‹1 THEN ์กฐ๊ฑด์‹ 1์˜ ๊ฒฐ๊ณผ
                    WHEN ์กฐ๊ฑด์‹2 THEN ์กฐ๊ฑด์‹ 2์˜ ๊ฒฐ๊ณผ    
                    .......
                    WHEN ์กฐ๊ฑด์‹n THEN ์กฐ๊ฑด์‹ n์˜ ๊ฒฐ๊ณผ
                                ELSE ๋‹ค๋ฅธ ๊ฒฐ๊ณผ
             END

์ด๋•Œ ELSE๋Š” ์ƒ๋žต๊ฐ€๋Šฅํ•˜๋‹ค
END๋Š” ์•ˆ๋ถ™์ด๋ฉด ์˜ค๋ฅ˜๊ฐ€ ๋ฐœ์ƒํ•œ๋‹ค.

SELECT CASE '01' WHEN '01' THEN 'A'
                 WHEN '02' THEN 'B'
                 WHEN '03' THEN 'C'
                 ELSE 'ETC'
        END"TEST"
    FROM DUAL;
-- A
SELECT CASE '01' WHEN '02' THEN 'A'
                 WHEN '01' THEN 'B'
                 WHEN '03' THEN 'C'
                 ELSE 'ETC'
        END"TEST"
    FROM DUAL;
--B
SELECT CASE '01' WHEN '03' THEN 'A'
                 WHEN '02' THEN 'B'
                 WHEN '01' THEN 'C'
                 ELSE 'ETC'
        END"TEST"
    FROM DUAL;
--C

SELECT CASE '11' WHEN '03' THEN 'A'
                 WHEN '02' THEN 'B'
                 WHEN '01' THEN 'C'
                 ELSE 'ETC'
        END"TEST"
    FROM DUAL;
--ETC

๐Ÿ“™์˜ˆ์ œ ๋ฌธ์ œ

๐Ÿ“–๋ฌธ์ œ1

SELECT NAME, TEL,
                 CASE SUBSTR(TEL,1,INSTR(TEL,')',1,1)-1)
                 WHEN '02' THEN 'SEOUL'
                 WHEN '031' THEN 'GYEONGGI'
                 WHEN '051' THEN 'BUSAN'
                 WHEN '052' THEN 'ULSAN'
                 WHEN '055' THEN 'GYEONGNAM'
                 ELSE 'ETC'
        END"LOC"
    FROM STUDENT
    WHERE DEPTNO1 =201;

๐Ÿ“–๋ฌธ์ œ 2

SELECT NAME
    , SUBSTR(JUMIN,3,2)"MONT"
    , CASE  SUBSTR(JUMIN,3,2)
    WHEN '01' THEN '1/4'
    WHEN '02' THEN '1/4'
    WHEN '03' THEN '1/4'
    WHEN '03' THEN '1/4'
    WHEN '04' THEN '2/4'
    WHEN '05' THEN '2/4'
    WHEN '06' THEN '2/4'
    WHEN '07' THEN '3/4'
    WHEN '08' THEN '3/4'
    WHEN '09' THEN '3/4'
    ELSE '4/4'
     END"Qua"
FROM STUDENT;

๋˜๋Š”

SELECT NAME
    , SUBSTR(JUMIN,3,2)"MONTH"
    , CASE 
    WHEN SUBSTR(JUMIN,3,2) between 01 and 03 THEN '1/4'
    WHEN SUBSTR(JUMIN,3,2) between 04 and 06 THEN '2/4'
    WHEN SUBSTR(JUMIN,3,2) between 07 and 09 THEN '3/4'
    ELSE '4/4'
     END"Qua"
FROM STUDENT;

๐Ÿ“–๋ฌธ์ œ 3

SELECT EMPNO
    , ENAME
    , SAL
    , CASE 
    WHEN SAL between 1 and 1000 THEN 'LEVEL 1'
    WHEN SAL between 1001 and 2000 THEN 'LEVEL 2'
    WHEN SAL between 2001 and 3000 THEN 'LEVEL 3'
    WHEN SAL between 3001 and 4000 THEN 'LEVEL 4'
    WHEN SAL >4000 THEN 'LEVEL 5'
    END"LEVEL"
    FROM EMP
    ORDER BY SAL DESC;

๐Ÿ“™์ •๊ทœ์‹(Regular Expression)

  • ์˜ค๋ผํด 10G ๋ถ€ํ„ฐ ์ถ”๊ฐ€๋จ

REGEXP_LIKE
REGEXP_REPLACE
REGEXP_COUNT
๋“ฑ๋“ฑ...


SELECT * 
    FROM T_REG;
    -- 12๊ฑด
    -- (์†Œ๋ฌธ์ž, ํŠน์ˆ˜๋ฌธ์ž, ์ˆซ์ž, ๋Œ€๋ฌธ์ž ๋“ฑ์ด ๋“ค์–ด์žˆ์Œ)
    
SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[a-z]');
    -- 6๊ฑด (์†Œ๋ฌธ์ž ์•ŒํŒŒ๋ฒณ์ด ๋“ค์–ด์žˆ์Œ)
    -- abc 123, abc  123, a1b2c3, aabbcc123, 123abc, abc

SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[A-Z]');
    -- 3๊ฑด (๋Œ€๋ฌธ์ž ์•ŒํŒŒ๋ฒณ์ด ๋“ค์–ด์žˆ์Œ)
    
SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[a-zA-Z]');
    -- 9๊ฑด (๋Œ€๋ฌธ์ž, ์†Œ๋ฌธ์ž ์•ŒํŒŒ๋ฒณ์ด ๋“ค์–ด์žˆ์Œ)
    
    
SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[a-z] ');
    -- 2๊ฑด, ์†Œ๋ฌธ์ž ์žˆ๊ณ , ๋‹ค์Œ์นธ์ด ๊ณต๋ฐฑ์ธ ๊ฒฝ์šฐ
    
SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[a-z] [0-9]');
    -- ์†Œ๋ฌธ์ž๊ฐ€ ์žˆ๊ณ  1๋ฒˆ์˜ ๊ณต๋ฐฑ ๋’ค ๋‹ค์Œ์นธ์ด ์˜์–ด์ธ ๊ฒฝ์šฐ
    
SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[a-z]  [0-9]');
    -- 1๊ฑด ์†Œ๋ฌธ์ž๊ฐ€ ์žˆ๊ณ , 2๋ฒˆ์˜ ๊ณต๋ฐฑ ๋’ค ์ˆซ์ž๊ฐ€ ์˜ค๋Š” ๊ฒฝ์šฐ
    -- abc  123
    
SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[a-z]   [0-9]');
    -- 0๊ฑด ์†Œ๋ฌธ์ž๊ฐ€ ์žˆ๊ณ , 3๋ฒˆ์˜ ๊ณต๋ฐฑ ๋’ค ์ˆซ์ž๊ฐ€ ์˜ค๋Š” ๊ฒฝ์šฐ

SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[[:space:]]');
    -- ๋ฌธ์ž ๋’ค์— ๊ณต๋ฐฑ์ด ์˜ค๋Š” ๊ฒฝ์šฐ 4๊ฑด
    -- space๋Š” ๋ฌด์กฐ๊ฑด ์†Œ๋ฌธ์ž๋งŒ ์ธ์‹ํ•จ. 
    -- ๋Œ€๋ฌธ์ž๋กœ SPACE ์ž‘์„ฑํ•  ๊ฒฝ์šฐ ์ธ์‹X
    
SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[a-z]{1}');
    -- ์†Œ๋ฌธ์ž 1์ž ์ด์ƒ 6๊ฑด
    
    
SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[a-z]{2}');
    -- ์†Œ๋ฌธ์ž 2์ž ์ด์ƒ    5๊ฑด
    
SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[a-z]{3}');
    -- ์†Œ๋ฌธ์ž 3์ž ์ด์ƒ   5 ๊ฑด
    
SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[a-z]{4}');
    -- ์†Œ๋ฌธ์ž 4์ž ์ด์ƒ    1๊ฑด    
SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[a-z]{5}');
    -- ์†Œ๋ฌธ์ž 5์ž ์ด์ƒ    1๊ฑด
    
SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[a-z]{6}');
    -- ์†Œ๋ฌธ์ž 6์ž ์ด์ƒ    1๊ฑด

SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[a-z]{7}');
    -- ์†Œ๋ฌธ์ž 7์ž ์ด์ƒ   0๊ฑด    
    
SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[0-9]');
    -- ์ˆซ์ž๊ฐ€ ํฌํ•จ ๋œ ๊ฒฝ์šฐ 9๊ฑด
    
SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[0-9]{2}'); 
    --์—ฐ์†์œผ๋กœ ์ˆซ์ž๊ฐ€ 2์ž ์ด์ƒ ๋‚˜์˜ค๋Š” ๊ฒฝ์šฐ 8๊ฑด

SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[0-9]{3}'); 
    --์—ฐ์†์œผ๋กœ ์ˆซ์ž๊ฐ€ 3์ž ์ด์ƒ ๋‚˜์˜ค๋Š” ๊ฒฝ์šฐ 8๊ฑด 

SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[0-9]{4}'); 
    --์—ฐ์†์œผ๋กœ ์ˆซ์ž๊ฐ€ 4์ž ์ด์ƒ ๋‚˜์˜ค๋Š” ๊ฒฝ์šฐ 1๊ฑด
    
SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[0-9]{5}'); 
    --์—ฐ์†์œผ๋กœ ์ˆซ์ž๊ฐ€ 5์ž ์ด์ƒ ๋‚˜์˜ค๋Š” ๊ฒฝ์šฐ 1๊ฑด 
    
SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[0-9]{6}'); 
    --์—ฐ์†์œผ๋กœ ์ˆซ์ž๊ฐ€ 6์ž ์ด์ƒ ๋‚˜์˜ค๋Š” ๊ฒฝ์šฐ 1๊ฑด    
    
SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[0-9]{7}'); 
    --์—ฐ์†์œผ๋กœ ์ˆซ์ž๊ฐ€ 2์ž ์ด์ƒ ๋‚˜์˜ค๋Š” ๊ฒฝ์šฐ 0๊ฑด       
    
SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[a-z][0-9]{1}'); 
    -- ์†Œ๋ฌธ์ž๋’ค์— ์ˆซ์ž๊ฐ€ 1๊ฐœ ์ด์ƒ ๋‚˜์˜ค๋Š” ๊ฒฝ์šฐ 1๊ฑด

SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[a-z][0-9]{2}'); 
    -- ์†Œ๋ฌธ์ž ๋’ค์— ์ˆซ์ž๊ฐ€ ์—ฐ๋‹ฌ์•„ 2๊ฐœ ์ด์ƒ ๋‚˜์˜ค๋Š” ๊ฒฝ์šฐ 1๊ฑด
    
SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[a-z][0-9]{3}'); 
    -- ์†Œ๋ฌธ์ž ๋’ค์— ์ˆซ์ž๊ฐ€ ์—ฐ๋‹ฌ์•„ 3๊ฐœ ์ด์ƒ ๋‚˜์˜ค๋Š” ๊ฒฝ์šฐ 1๊ฑด

SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[a-z][0-9]{4}'); 
    -- ์†Œ๋ฌธ์ž ๋’ค์— ์ˆซ์ž๊ฐ€ ์—ฐ๋‹ฌ์•„ 4๊ฐœ ์ด์ƒ ๋‚˜์˜ค๋Š” ๊ฒฝ์šฐ 0๊ฑด
    
SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[a-zA-Z]'); 
    -- ์†Œ๋ฌธ์ž์™€ ๋Œ€๋ฌธ์ž ๋‘๊ฐœ ๋‹ค ์žˆ๋Š” ๊ฒฝ์šฐ

    
SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'^[a-zA-Z]'); 
    -- ์ฒซ๊ธ€์ž๊ฐ€ ๋Œ€๋ฌธ์ž ๋˜๋Š” ์†Œ๋ฌธ์ž์ธ ๊ฒฝ์šฐ 8๊ฑด 
    
SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'^[0-9A-Z]'); 
    -- ์ฒซ๊ธ€์ž๊ฐ€ ์ˆซ์ž ๋˜๋Š” ๋Œ€๋ฌธ์ž์ธ ๊ฒฝ์šฐ
    
SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[a-z]$'); 
    -- ์†Œ๋ฌธ์ž๋กœ ๋๋‚˜๋Š” ๊ฒฝ์šฐ
    --123abc, abc

SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[A-Z]$'); 
    -- ๋Œ€๋ฌธ์ž๋กœ ๋๋‚˜๋Š” ๊ฒฝ์šฐ 0๊ฑด

SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[0-9]$'); 
    -- ์ˆซ์ž๋กœ ๋๋‚˜๋Š” ๊ฒฝ์šฐ
    -- ABC123, ABC 123, ABC  123, abc 123, abc  123, a1b2c3, aabbcc123,123123 

SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[^a-z]'); 
		-- ์†Œ๋ฌธ์ž๋กœ ์‹œ์ž‘ํ•˜์ง€ ์•Š๋Š” ๊ฒฝ์šฐ

SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[^0-9]'); 
    -- ์ˆซ์ž๋กœ ์‹œ์ž‘๋˜์ง€ ์•Š๋Š” ๊ฒฝ์šฐ

SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'^[^0-9a-z]'); 
    -- ์†Œ๋ฌธ์ž๋‚˜ ์ˆซ์ž๋กœ ์‹œ์ž‘๋˜์ง€ ์•Š๋Š” ๊ฒฝ์šฐ 5๊ฑด
    --ABC123, ABC 123, ABC  123, ?/!@#$*&, \~*().,
    
    
SELECT * 
    FROM T_REG
    WHERE REGEXP_LIKE(TEXT,'[^a-z]'); 
    -- ์†Œ๋ฌธ์ž๋กœ๋งŒ ๊ตฌ์„ฑ๋œ ๊ฒƒ์ด ์•„๋‹Œ ๊ฒฝ์šฐ
    --(์†Œ๋ฌธ์ž๋กœ๋งŒ ๊ตฌ์„ฑ๋œ๊ฒƒ์ด ์•„๋‹Œ, ๋Œ€๋ฌธ์ž, ์ˆซ์ž, ํŠน์ˆ˜๋ฌธ์ž๋“ค์ด ์žˆ๋‹ค๋Š” ๋ง)
    --ABC123
		--ABC 123
		--ABC  123
		--abc 123
		--abc  123
		--a1b2c3
		--aabbcc123
		--?/!@#$*&,~*().
		--123123
		--123abc
    
 SELECT * 
    FROM T_REG
    WHERE NOT REGEXP_LIKE(TEXT,'[a-z]');   
    -- ์†Œ๋ฌธ์ž๊ฐ€ ๋“ค์–ด์žˆ๋Š” ๊ฒƒ๋“ค์€ ๋‹ค ๋ฐฐ์ œํ•œ ๊ฒƒ๋“ค
    --ABC123, ABC 123 ,ABC  123 ,?/!@#$*&,\~*()., ,123123

SELECT * 
    FROM T_REG
    WHERE NOT REGEXP_LIKE(TEXT,'[a-zA-Z]');
    --์†Œ๋ฌธ์ž ๋Œ€๋ฌธ์ž ๋“ค์–ด๊ฐ„๊ฒƒ๋“ค์€ ๋‹ค ๋ฐฐ์ œ ํ•œ๊ฒƒ๋“ค
    
SELECT * 
    FROM T_REG
    WHERE NOT REGEXP_LIKE(TEXT,'[0-9]');
-- ์ˆซ์ž๊ฐ€ ๋“ค์–ด๊ฐ„๊ฒƒ๋“ค์€ ๋ฐฐ์ œํ•œ๊ฒƒ๋“ค



SELECT NAME
    , ID
    FROM STUDENT
    WHERE REGEXP_LIKE(ID,'^M(a|o)'); 
    -- ์•„์ด๋”” ๋งจ ์•ž์— ๋Œ€๋ฌธ์ž M์ด ์˜ค๊ณ  ๋’ค์— a๋‚˜ o ๊ฐ€ ์˜ค๋Š” ๊ฒฝ์šฐ
    
    
SELECT NAME
    , ID
    FROM STUDENT
    WHERE REGEXP_LIKE(ID,'^M(a|o)'); 
    -- ์•„์ด๋”” ๋งจ ์•ž์— ๋Œ€๋ฌธ์ž M์ด ์˜ค๊ณ  ๋’ค์— a๋‚˜ o ๊ฐ€ ์˜ค๋Š” ๊ฒฝ์šฐ

๐Ÿ“–์˜ˆ์ œ - REGEXP_LIKE

STUDENT ํ…Œ์ด๋ธ”์—์„œ ์ง€์—ญ๋ฒˆํ˜ธ๊ฐ€ 2์ž๋ฆฌ์ด๊ณ , ๊ทธ ๋‹ค์Œ ๊ตญ๋ฒˆ์ด ์—ฐ์†์ ์œผ๋กœ 4์ž๋ฆฌ๊ฐ€ ๋‚˜์˜ค๋Š” ๊ฐ’ ์ถœ๋ ฅํ•˜๊ธฐ

SELECT NAME
    , TEL
    FROM STUDENT
    WHERE REGEXP_LIKE(TEL,'^[0-9]{2}\)[0-9]{4}');    
    -- ํŠน์ˆ˜ ๋ฌธ์ž๋ฅผ ์“ฐ๊ธฐ ์œ„ํ•ด์„œ๋Š” ์•ž์— ์—ญ์Šฌ๋ž˜์‹œ\๋ฅผ ์‚ฌ์šฉํ•˜๋ฉด ๋œ๋‹ค.

SELECT NAME
    , TEL
    FROM STUDENT
    WHERE ((INSTR(TEL,')')-1)= 2)
    AND
    REGEXP_LIKE(TEL,'[)][0-9]{4}');

๐Ÿ“–์˜ˆ์ œ - REGEXP_REPLACE

SELECT TEXT
   , REGEXP_REPLACE(TEXT,'[0-9]','*') "NO->CHAR"
   FROM T_REG;
-- ์ˆซ์ž๋ฅผ *๋กœ ๋ฐ”๊ฟ”์คŒ.

T_REG2 ํ…Œ์ด๋ธ”์—์„œ IP์˜ .(DOT)๋ถ€๋ถ„ ๋ชจ๋‘ ์‚ญ์ œํ•˜๊ณ  ์ถœ๋ ฅ

SELECT no
    , IP
    , REGEXP_REPLACE(IP,'\.','/',1,1) "Dot Remove"
    FROM T_REG2;

๐Ÿ“–์˜ˆ์ œ 2 -REGEXP_REPLACE

SELECT no
   , IP
   , REGEXP_REPLACE(IP,'\.','/',1,1) "REPLACE1"
   , REGEXP_REPLACE(IP,'\.','/',4,1) "REPLACE1"
   , REGEXP_REPLACE(IP,'\.','/',4,2) "REPLACE1"
   , REGEXP_REPLACE(IP,'\.','/',4,2) "REPLACE1"
   , REGEXP_REPLACE(IP,'\.','/',4,2) "REPLACE1"
   FROM T_REG2;

์˜ˆ์ œ 3 -REGEXP_REPLACE

SELECT  REGEXP_REPLACE('aaa bb  c   d','( ){1}',' ') "a"
   -- ๊ณต๋ฐฑ ํ•˜๋‚˜ ์ด์ƒ์ธ ๋ถ€๋ถ„์€ ์—†์•ค๋‹ค.
    ,REGEXP_REPLACE('aaa bb  c   d','( ){1}',' ') "b"
       -- ๊ณต๋ฐฑ ํ•˜๋‚˜์ธ ๋ถ€๋ถ„์„ ๊ณต๋ฐฑ์œผ๋กœ ๋ฐ”๊พผ๋‹ค.(์‹ค์งˆ์ ์œผ๋กœ ๋ณ€ํ™” ์—†๋Š” ๊ฒฝ์šฐ)
    ,REGEXP_REPLACE('aaa bb  c   d','( ){2}','') "c"
    -- ๊ณต๋ฐฑ 2๊ฐœ์ธ ๋ถ€๋ถ„์€ ์—†์•ค๋‹ค.(๊ณต๋ฐฑ 1๊ฐœ์žˆ๋Š” ๊ณณ์€ ๋„˜์–ด๊ฐ)
    ,REGEXP_REPLACE('aaa bb  c   d','( ){3}','') "d"
    -- ๊ณต๋ฐฑ 3๊ฐœ์ธ ๋ถ€๋ถ„์€ ์—†์•ค๋‹ค.(๊ณต๋ฐฑ 1,2๊ฐœ์žˆ๋Š” ๊ณณ์€ ๋„˜์–ด๊ฐ)
    ,REGEXP_REPLACE('aaa bb  c   d','( ){2,}','') "e"
    -- ๊ณต๋ฐฑ 2๊ฐœ ์ด์ƒ์ธ ๋ถ€๋ถ„์„ ์—†์•ค๋‹ค. 
    ,REGEXP_REPLACE('aaa bb  c   d','( ){3,}','') "f"
    -- ๊ณต๋ฐฑ 3๊ฐœ ์ด์ƒ์ธ ๋ถ€๋ถ„์—†์•ค๋‹ค.
    ,REGEXP_REPLACE('aaa bb  c   d','( ){1,}','') "g"
    --๊ณต๋ฐฑ 1๊ฐœ์ด์ƒ์ธ ๊ณณ์„ ์—†์•ฐ(๊ณต๋ฐฑ ์•„์˜ˆ ์—†์–ด์ง)
    FROM DUAL;

๐Ÿ“–์˜ˆ์ œ 4 -REGEXP_REPLACE

SELECT REGEXP_REPLACE('aaa bbb','( ){2, }','*') "*"

   , REGEXP_REPLACE('aaa  bbb','( ){2, }','*') "*"
   , REGEXP_REPLACE('aaa   bbb','( ){2, }','*') "*"
   , REGEXP_REPLACE('aaa   bbb','( ){2}','*') "*"
    FROM DUAL;

๐Ÿ“–REGEXP_COUNT

SELECT TEXT
    ,REGEXP_COUNT(TEXT,'a')"C"
    -- TEXT์˜ ์†Œ๋ฌธ์ž a์˜  ๊ฐฏ์ˆ˜
    ,REGEXP_COUNT(TEXT,'bc')"C"
    -- ์†Œ๋ฌธ์ž bc์˜ ๊ฐฏ์ˆ˜
    ,REGEXP_COUNT(TEXT,'12')"C"
    -- ์ˆซ์ž 12์˜ ๊ฐฏ์ˆ˜
    ,REGEXP_COUNT(TEXT,'c',5)"C"
    -- 5๋ฒˆ์งธ ๊ธ€์ž ์ดํ›„๋กœ ์†Œ๋ฌธ์ž c์˜ ๊ฐฏ์ˆ˜
     ,REGEXP_COUNT(TEXT,'c')"C"
    --์†Œ๋ฌธ์ž c์˜ ๊ฐฏ์ˆ˜
    ,REGEXP_COUNT(TEXT,'c',1,'i')"C"
    --'i'๋Š” ๋Œ€์†Œ๋ฌธ์ž ๋‘˜ ๋‹ค ํ•ด๋‹น๋จ. 
    -- ์ฒซ๋ฒˆ์งธ ์ž๋ฆฌ๋ถ€ํ„ฐ ๋Œ€์†Œ๋ฌธ์ž ๊ตฌ๋ณ„ ์—†์ด c์˜ ๊ฐฏ์ˆ˜
    ,REGEXP_COUNT(TEXT,'c',5,'i')"C"
    --'i'๋Š” ๋Œ€์†Œ๋ฌธ์ž ๋‘˜ ๋‹ค ํ•ด๋‹น๋จ. 
    -- 5๋ฒˆ์งธ ์ž๋ฆฌ๋ถ€ํ„ฐ ๋Œ€์†Œ๋ฌธ์ž ๊ตฌ๋ณ„ ์—†์ด c์˜ ๊ฐฏ์ˆ˜
     ,REGEXP_COUNT(TEXT,'.')"C"
     -- ๊ณต๋ฐฑ๊ณผ ๊ธ€์ž ํฌ๊ธฐ(TEXT์˜ ๊ธธ์ด)
     -- =LENGTH (TEXT)
     , LENGTH (TEXT)"C"
    ,REGEXP_COUNT(TEXT,'\.')"C"
    -- ํŠน์ˆ˜ ๋ฌธ์ž . ์ด ์žˆ๋Š” ๊ฐฏ์ˆ˜
    ,REGEXP_COUNT(TEXT,'aa')"V"
    -- aa ์ด ์žˆ๋Š” ๊ฐฏ์ˆ˜
    ,REGEXP_COUNT(TEXT,'a{2}')"V"
    -- a๊ฐ€ ์—ฐ์† 2๊ฐœ๊ฐ€ ์žˆ๋Š”์ง€
    ,REGEXP_COUNT(TEXT,'{a}{a}')"V"
    -- a๊ฐ€ ์—ฐ์† 2๊ฐœ๊ฐ€ ์žˆ๋Š”์ง€
    --"V" 4๊ฐœ๋‹ค ๋™์ผํ•œ ์˜๋ฏธ
    FROM T_REG;

profile
sofia
์ด์ „ ํฌ์ŠคํŠธ

2022.07.26 - SQL(1) (๋ณต์Šต๋ฌธ์ œ)

๋‹ค์Œ ํฌ์ŠคํŠธ

2022.08.02 - SQL

0๊ฐœ์˜ ๋Œ“๊ธ€