16. 정규식

■ 정규식 지원

(3권 268페이지)

문자열 데이터의 간단한 패턴 또는 복잡한 패턴을

검색하고 조작할 수 있게 해주는 함수.

 

regexp = regular expression = 정규표현식

 

*정규식 함수의 종류

1. regexp_like

2. regexp_replace

3. regexp_instr

4. regexp_substr

5. regexp_count

 

*Meta 문자중에 독특한 성질을 지니고 있는 문자클래스'[ ]'라는 문자가 있습니다. 문자클래스는 그 내부에 해당하는 문자열의 범위 중 한 문자만 선택한다는 의미이며, 문자클래스 내부에서는 Meta문자를 사용할 수 없거나 의미가 다르게 사용됩니다.

 

*POSIX에서만 사용하는 문자클래스가 있는데, 단축키처럼 편리하게 사용할 수 있습니다. 대표적인 POSIX 문자클래스는 다음과 같으며 대괄호'[ ]' 가 붙어있는 모양 자체가

표현식이므로 실제로 문자클래스로 사용할 때에는

대괄호를 씌워서 사용해야만 정상적인 결과를 얻을 수 있습니다.

 

 

 

■ regexp_like

REGULAR EXPRESSION 을 통해 단일행 연산자 LIKE 를

한번에 처리하게 해준다

 

문제523.

이름에 EN 또는 IN 을 포함하고 있는 사원들의

이름과 월급을 출력하시오

 

기존에 배운 방식으로 풀면.

 select ename, sal

 from emp

where ename like '%EN%'

       or

         ename like '%IN%';

 

 

select ename, sal

from emp

 where regexp_like(ename, 'EN|IN' );

※설명: | 는 '또는' 의 뜻이다.

 

 

 

문제524.

전공이 심리학과, 통계학과, 프랑스어학과, 물리학과 인

학생들의 이름과 전공을 출력하시오

 

select ename, major

from emp2

where regexp_like(major, '심리|통계|프랑스|물리');

 

 

12c 로 접속해서 아래의 쿼리를 작성하시오.

 create  table  employees

  2  as

  3   select * from hr.employees;

 

count  해보면 107개 나옴

 

xe로 접속한 11g 에다가

CREATE  DATABASE LINK  link_12c

CONNECT  TO  scott

IDENTIFIED  BY  tiger

USING 'localhost:1522/pdborcl';

 

SELECT * FROM  employees@link_12c;

 

우리가 계속 쓰던 xe 는 11g 의 데이터베이스.

orcl 은 12c 의 데이터베이스.

근데 링크 걸어서 11g 에서도 씀.

 

타자치기 편하게

create table employees

as select * from employees@link_12c;

 

 

 

문제525.

12c 에 hr 계정의 테이블에 접근할 수 있는 디비링크를

아래와 같이 생성하시오.

 

CREATE  DATABASE LINK  link_12c_hr

CONNECT  TO  hr

IDENTIFIED  BY  hr

USING 'localhost:1522/pdborcl';

 

select * from tab@link_12c_hr;

 

이제 이런 테이블들을 다 갖고와서 쓸 수 있게 되었다.

 

 

문제526.(점심시간문제)

12c 의 hr 계정의 모든 테이블들과

12c 의 sh 계정의 모든 테이블들을 다

11g   xe 의 scott 계정에 생성하시오.

 

CREATE  DATABASE LINK  link_12c_hr

CONNECT  TO  hr

IDENTIFIED  BY  hr

USING 'localhost:1522/pdborcl';

 

select * from tab@link_12c_hr;

 

 

 

CREATE  DATABASE LINK  link_12c_sh

CONNECT  TO  sh

IDENTIFIED  BY  sh

USING 'localhost:1522/pdborcl';

 

select * from tab@link_12c_sh;

 

 

우리가 옮긴 테이블 중에서

sales 가 제일 큰 건인데

count 하면 91만건

 

 

*정규식 함수의 종류

1. regexp_like

2. regexp_replace

3. regexp_instr

4. regexp_substr

5. regexp_count

 

■regexp_like

문제527.

employees 테이블에서 first_name 이 Steven 인 사원의

first_name, email, phone_number 를 출력하시오!

select first_name, email, phone_number

from employees

where first_name = 'Steven';

 

 

문제528.

이름의 첫글자가 St 로 시작하면서 끝글자가 en 으로

끝나는 사원들의 first_name 을 출력하시오

 

select first_name

from employees

where first_name like 'St%en';

 

substr 로 해보면

select first_name

   from employees

  where substr(first_name,1,2) = 'St' and first_name like '%en';

 

 

select first_name

from employees

where regexp_like(first_name , '^Ste(.)+en$');

 

※설명:273-274페이지

• ^: 문자열의 시작
• $: 문자열의 끝
• ( ): 괄호로 묶은식은 한단위로 나타냄
• .     : 문자 자리 한 개
• +    : 한 번 이상 발생한 수 일치

 

■ ^Ste(.)+en$ : Ste로 시작해라.

               (.)+ 문자 한 단위가 몇개가 되든 상관없다

               en$  문자열의 끝이 en 이다.

 

 

 

문제528.

성씨가 김씨 또는 이씨로 시작하고 이름의 끝글자가 '진' 인

학생의 이름을 출력하시오.

 

오답

select ename

from emp2

where regexp_like (ename, '^김|이(.)+진$');

 

*김 또는 이를 다 뽑고 끝에 진인걸 뽑으라는 뜻.

 

 

답

select ename

     from emp2

     where regexp_like ( ename, '^김(.)+진$|^이(.)+진$');

 

또는

select ename

from emp2

where regexp_like (ename, '^(김|이)(.)+진$');

 

 

 

■regexp_replace

REGULAR EXPRESSION 과 REPLACE 를 결합한 것으로

구간을 출력하거나

PATTERN 을 넣어서 원하는 값을 출력할 수 있다.

 

 

문제529.

이름과 월급을 출력하는데

월급을 출력할 때 replace 함수를 이용해서

숫자 0을 *로 출력하시오

 

오답

select ename, sal

from emp

where replace(sal, 0, *);

******replace가 바꿔서 출력하라 라는 함수.

 

답

select ename, sal, replace(sal, 0, '*')

from emp;

 

 

 

문제530.

이름과 월급을 출력하는데 월급 숫자 0~3까지를 *로 출력하시오

select ename, regexp_replace(sal, '[0-3]', '*')

from emp;

 

 

 

 

문제531.

우리반 학생의 이름을 출력하는데

중간글자가 *로 나오게 출력하시오

Regexp_replace 사용해서(난이도 높음)

 

select replace(ename,substr(ename,2,1),'*')

from emp2;

 

regexp_replace 로 하려면?

select substr(ename, 1,1)||regexp_replace(ename, '(.)+', '*')||substr(ename, -1, 1)

from emp2;

 

regexp_replace(ename, '(.)+', '*')

      ----->> 이름의 여러글자를 *로 출력하는데 중복제거.

              =결국  이름을 * 하나로 뽑으라는 소리.

 

 

번외문제.

우리반 테이블에서 이메일의 도메인(ex: naver)을

*로 나오게 출력하시오

 

select regexp_replace

(email,substr(email, instr(email, '@')+1, instr(email,'.')-1 - instr(email,'@')) , '*')이메일

from emp2;

 

 

문제532.

employees 에서 phone_number를 아래와 같이 출력하시오

 

select phone_number from employees;

 

숫자사이 점을 '-' 로 바꾸시오

 

select regexp_replace(phone_number,  '\.', '-')

from employees;

 

 select replace(phone_number, substr(phone_number,4,1),'-')

   from employees;

 substr(phone_number,4,1) =  ' . '

 

■regexp_count

REGULAR EXPRESSION과 COUNT 의 결합으로

장문의 문장에서 원하는 단어의 수를 파악할 수 있게 해준다.

 

SELECT REGEXP_COUNT(

'ccacctttccctccactcctcacgttctcacctgtaaagcgtccctccctcatccccatgcccccttaccctgcag

ggtagagtaggctagaaaccagagagctccaagctccatctgtggagaggtgccatccttgggctgcagagagaggag

aatttgccccaaagctgcctgcagagcttcaccacccttagtctcacaaagccttgagttcatagcatttcttgagtt

ttcaccctgcccagcaggacactgcagcacccaaagggcttcccaggagtagggttgccctcaagaggctcttgggtc

tgatggccacatcctggaattgttttcaagttgatggtcacagccctgaggcatgtaggggcgtggggatgcgctctg

ctctgctctcctctcctgaacccctgaaccctctggctaccccagagcacttagagccag',

'gtc') AS Count

FROM dual;

 

regexp_count(지정값, 지정값내 추출하고자 하는 문자)

 

 

 

문제533.

안철수 연설문에서 '국민'이라는 단어가 몇 번 나오는지

카운트 하시오

 

select regexp_count(

'안녕하십니까 안철수입니다.

저는 지난 7월말에 말씀 드린 대로 국민들의 의견을 듣고자 많은 분들을 만났습니다.

제가 좋아하는 작가, 윌리엄 깁슨의 말을 하나 소개하고 싶습니다.

'미래는 이미 와 있다. 단지 널리 퍼져있지 않을 뿐이다'

그렇습니다. 미래는 지금 우리 앞에 있습니다.

고맙습니다'

 ,'국민')

from dual;

********중간중간 싱글쿼테이션 지워야함

결국 너무 길어서 안 나옴 **********

 

 

 

문제533.

겨울왕국 대본에는 elsa 가 몇 번 나오는지

regexp_count 로 카운트 하시오!

 

select sum(aaa)

from(select regexp_count(lower(win_text), ' elsa ') aaa

from winter_kingdom

     );

***elsa 앞뒤로 공백***

Sum 을 안 하면?????????????????????????????

: elsa 가 등장한 row 수 가 다 나오고

해당 로우에 등장한 엘사수만 카운트함.

Ex:

select regexp_count(lower(email), 's')aaa

from emp2;

 

 

■regexp_instr

REGULAR와 EXPRESSION을 INSTR 과 결합한 정규식으로

INSTR 의 단순기능 뿐만 아니라

지정값에서 문자, 숫자, 특수문자만을 지정출력 가능하다.

 

(3권 279페이지)

문제534.

우리반 테이블에서 이메일을 출력하고

그 옆에 이메일에 '@' 가 몇 번째 자리에 있는지

출력하시오

 

select email, regexp_instr(email, '[@]')

from emp2

 *** [ ] 안 써도 나옴

 

아까 점심시간 넣은 locations 테이블

select * from locations;

 

 

문제535.

locations 테이블에 street_address 데이터에서

알파벳으로 시작되는 부분의 자리위치가

어떻게 되는지

확인하시오

 

select street_address,

 regexp_instr(street_address, '[[:alpha:]]')

from locations;

 

 

 

 

 

 

 

문제536.

위의 문제를 다시 출력하는데

알파벳이 아닌 곳의 위치번호를 출력하시오

 

select street_address,

 regexp_instr(street_address, '\d')

from locations;

 

***숫자가 시작되는 부분을 찾아낸거

    기호, 부호는 읽어내지 못해서 0으로 처리한다.16번참고.

 

 

 

select street_address,

 regexp_instr(street_address, '[^[:alpha:]]')

from locations;

 

 

****알파벳이 아닌 그 어떤 것이 시작되는 부분을 찾는것

(숫자, '.' 같은 기호 모두 다) 16번을 보면 다른값이 나왔음을 알 수 있다.

 

 

■regexp_substr

REGULAR, EXPRESSION 을 SUBSTR 과 결합한 정규식으로

일반 SUBSTR 기능 뿐만 아니라

PATTERN 을 넣어서 지정조건값을 출력가능하게 해준다.

 

문제537.

우리반 테이블에 이메일에 체크제약을 거는데

이메일에 @ 가 포함되어 있지 않으면

데이터가 입력되지 못하게 하시오!

 

alter table emp2

 add constraint emp2_email_ck check(regexp_like(email, '@'))

 

 

 

번외문제

우리반 테이블에서 email 에서 @ 뒤를 출력하시오.

 

 

select email,

 regexp_substr(email, '[^@]+', 1,2)

 from emp2;

 

**[^@]+

   여기서 + 가 빠지면

 기능을 안한다. @부터 시작하라는 사인도 안 먹힌다.

그냥 substr(email, 1,2) 한 것과 똑같아진다.  

select email,

 regexp_substr(email, '[^@]', 1,2)

 from emp2;

 

 

마지막 숫자가 '[^@]+' 기준으로 '뒷블럭' 이라는건 알겠다.

그럼 앞에 숫자(예시에서 4) 의 의미는??

>>앞의 숫자는 ^@ 전의 갯수만 의미한다.

  (즉 sehee5542@ 에서

       sehee5542 만 카운트 한다)

예:

select email,

 regexp_substr(email, '[^@]+', 4,2)

 from emp2;

                                   ^

                              이 숫자는

                       기준이 어디냐를 떠나서

                      무조건 앞에서 부터 센다

위 쿼리로 돌려보면

'dev@xerato.com' 이라는 이메일은 null 값 으로 나온다.

왜냐하면 ('[^@]+, 4, 2) 는

뒷블럭(=2) 출력, 앞블럭의 4번째 문자 지정 이라는 뜻이다.

Dev는 3개니까 4번째가 없어서 null 로 나온다.

 

사실 @뒤 를 출력하기 위해서는

무의미한 값이지만

프로그램상 넣어야 하는 값이니 그냥 1로 해주면 된다.

 

([^@]+, 1) 을 셋트로 외우자.

참고로 지정한 @ 는 출력안된다.(기준으로 쓰여서)

 

 

 

문제538.

김용신 라디어 선곡표에서 아래와 같이

가수명만 잘라서 출력하시오.

 

select m_name,regexp_substr(m_name,'[^-]+',1,2) 가수

from music

WHERE INSTR(m_name, '-')<>0;

 

 

SELECT m_name, substr(m_name, instr(m_name,'-')+1) 가수

FROM MUSIC

WHERE INSTR(m_name, '-')<>0;

진짜 null 해결방법이 감탄스럽다…….

 

 

 

select m_name,regexp_substr(m_name,'[^-]+',1,1) 노래

from music

WHERE INSTR(m_name, '-')<>0;

regexp_substr(m_name, '[^-]+') 하면 하이픈을 기준으로

앞 뒤로 2뭉탱이로 나눠진다.

 

 

문제539.

김용신 라디오 선곡표에서 가장 많이 나오는 가수명과

건수와 순위를 출력하시오.

 

create view cbs_radio

as

select m_name, regexp_substr(m_name, '[^-]+' ,1,2) 가수

from music

where instr(m_name, '-')<>0;

 

****null 처리를 어찌 이런

 

select trim(가수), count(*) cnt, dense_rank()over(order by count(*) desc)

from cbs_radio

group by trim(가수)

order by cnt desc;

 

 

 regexp_substr(m_name, '[^-]+' , 1, 2) 가수

                                       

regexp_substr(m_name, '[^-]+', 1, 1)노래

 

 

 

문제540. (sql 마지막 문제)

cbs 라디오 김용신 노래 선곡표에서 가장 많이 나오는

노래명과 건수와 순위를 출력하시오

숫자를 제낀 노래명 - 가수명, 건수, 순위를 출력하시오

 

select trim(substr(m_name, instr(m_name, '.')+1) ) 노래가수, count(*) cnt , dense_rank() over (order by count(*) desc) 순위

from music

where instr(m_name, '-' ) <> 0

group by trim(substr(m_name, instr(m_name, '.')+1) ) ;

 

소현답

***substr 4 라고 한 건, 거기부터가 노래제목의 시작인 걸 알고 지정한건데

어떻게 아는건지 모르겠음.

with mmu 

as

(select trim(ltrim(substr(regexp_substr(m_name, '[^-]+',1,1),4),'.')) 곡이름, trim(regexp_substr(m_name, '[^-]+',1,2)) 가수

from music

where instr(m_name, '-') <>0)

select initcap(lower(곡이름 || ' - ' || 가수)), count(*) ct, dense_rank() over (order by count(*) desc) rnk

from mmu m

group by lower(곡이름 || ' - ' || 가수);