고급 쿼리 / 정규식

 

 

테이블 및 데이터 추가

-- reg 테이블
CREATE  TABLE  reg (
    num NUMBER(5) PRIMARY KEY
    ,name VARCHAR2(30)
    ,email VARCHAR2(100)
);


INSERT INTO reg (num, name, email) VALUES (1, '한라산', 'hanlasan@abc.co.kr');
INSERT INTO reg (num, name, email) VALUES (2, '백두산', 'backdusan@abc.co.kr');
INSERT INTO reg (num, name, email) VALUES (3, '금강산', 'gumgangsan@xyz.co.kr');
INSERT INTO reg (num, name, email) VALUES (4, '김선생', 'kim1@abc.co.kr');
INSERT INTO reg (num, name, email) VALUES (5, '김자바', 'kim22@abc.co.kr');
INSERT INTO reg (num, name, email) VALUES (6, '김하둡', 'kim323@tty.com');
INSERT INTO reg (num, name, email) VALUES (7, '김자두', 'kim333@xyz.co.kr');
INSERT INTO reg (num, name, email) VALUES (8, '후후후', 'hello@abc.co.kr');
INSERT INTO reg (num, name, email) VALUES (9, '아리랑', 'arirang@abc.co.kr');
INSERT INTO reg (num, name, email) VALUES (10, '이서울', 'seoul@sxt.com');
INSERT INTO reg (num, name, email) VALUES (11, '너너너', 'home@home.co.kr');
INSERT INTO reg (num, name, email) VALUES (12, '오라클', 'home2@home.COM');
INSERT INTO reg (num, name, email) VALUES (13, 'oracle', 'oracle@test.co,kr');
INSERT INTO reg (num, name, email) VALUES (14, '이상해', 'lee2@xtz.COM');
INSERT INTO reg (num, name, email) VALUES (15, 'kim java', 'java@ttt.COM');
COMMIT;

 

SELECT * FROM reg WHERE SUBSTR(name,1,1) IN ('한','백');

name의 첫번째 글짜가 '한,백'인 값들 출력

 

SELECT * FROM reg WHERE INSTR(name,'한') = 1;

마찬가지로 같은 결과지만 다른 함수를 사용

INSTR = 기본으로 왼쪽부터 시작하여 우측방향으로 스캔한다.

찾는 값이 있으면 1 없으면 0 반환

 

SELECT * FROM reg WHERE REGEXP_LIKE(name, '^[한백]');

마찬가지로 같은 결과지만 다른 함수를 사용

^ 는 시작을 의미함.

 

name이 강산으로 끝나는 레코드

SELECT * FROM reg WHERE REGEXP_LIKE(name,'강산$');

 

email 끝이 com 끝나는 애들 출력

SELECT * FROM reg WHERE REGEXP_LIKE(email,'com$');

소문자만 검색 가능하다.

SELECT * FROM reg WHERE REGEXP_LIKE(email,'com$','i');

뒤에 'i'를 붙여주면 대소문자 상관 없이 가져온다.

 

email에 kim이 포함 되어 있는 애들

SELECT * FROM reg WHERE REGEXP_LIKE(email,'kim');
SELECT * FROM reg WHERE REGEXP_LIKE(email, 'kim*');

 

kim3 + 어떤 글자든 상관 없음 + 3

SELECT * FROM reg WHERE REGEXP_LIKE(email, 'kim3?3');

-- 물음표 활용

 

kim 다음에 1이 아닌 애들만 출력

SELECT * FROM reg WHERE REGEXP_LIKE(email, 'kim[^1]');

 

k 로 시작 하지 않는 애들

SELECT * FROM reg WHERE REGEXP_LIKE(email, '^[^k]');

 

이름이 한글인 애들

SELECT * FROM reg WHERE REGEXP_LIKE(name, '^[가-힣]{2,}$');

 

숫자가 존재하는 애들

SELECT * FROM reg WHERE REGEXP_LIKE(email, '[[:digit:]]');

SELECT * FROM reg WHERE REGEXP_LIKE(email, '[0-9]');

 

영문자가 존재하는 애들

SELECT * FROM reg WHERE REGEXP_LIKE(email, '[a-z|A-Z]');

 

REGEXP_REPLACE

문자열을 지정한 패턴으로 바꾼다.

 

 

SELECT 'hong gil dong' FROM dual;

 

홍길동 이름을 길동홍으로 바꾸기

 

SELECT REGEXP_REPLACE('hong gil dong', '(.*) (.*) (.*)', '\2 \3 \1') FROM dual;

(.*) 부분 띄어쓰기 조심해야 한다.

 

 

kim을 김김으로 바꾸기

SELECT email, REGEXP_REPLACE(email, 'kim', '김김') FROM reg;

 

숫자 제거하기 (공백으로 바꿈)

SELECT email,  REGEXP_REPLACE(email, '[[:digit:]]', '') FROM reg;

 

숫자, 특수문자 제거 (vscode 안될수있음)

SELECT REGEXP_REPLACE('우리&*나라 12 대!@한', '[[:digit:]|[:punct:]]', '') FROM dual;

 

REGEXP_INSTR

패턴이 있는 위치 반환

 

영문자나 한글이 아닌 문자의 위치 (예를 들면 공백 같은거)

alpha는 한글과 영어 검색

SELECT name, REGEXP_INSTR(name, '[^[:alpha:]]') FROM reg;

 

SELECT name, REGEXP_INSTR(name, '[^[:lower:]]') FROM reg;

SELECT * FROM reg WHERE REGEXP_LIKE(name, '[a-z|A-Z]');

SELECT * FROM reg WHERE REGEXP_LIKE(name, '[[:lower:]]');

 

 

@앞으로 문자 자르기

SELECT email, REGEXP_SUBSTR(email, '[^@]+') FROM reg;

 

숫자 몇개인지 개수 세기

 SELECT email, REGEXP_COUNT(email, '[0-9]') FROM reg;