sql 단일행 함수 - 정규식

정규식 

정규 표현식

1. REGEXP_LIKE

like 함수처럼 특정 패턴과 매칭되는 결과를 검색하는 함수

사용 예 1.)

소문자 영문자가 들어있는 행 출력  - SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'[a-z]') ;

대문자 영문자가 들어있는 행 출력  - SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'[A-Z]') ;

대소문자 영문자가 들어있는 행 출력 - SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'[a-zA-Z]') ; 

사용 예 2.)

소문자로 시작하고 뒤에 공백이 있는 모든 행 출력 

- SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'[a-z]  ') ;

소문자로 시작하고 공백이 1칸 있고 숫자로 끝나는 행 출력

- SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'[a-z] [0-9]') ;

공백이 있는 모든 데이터를 찾고 싶은 경우

- SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'[[:SPACE:]]') ;

사용 예 3.)

대문자가 연속적으로 2글자 이상오는 경우 출력

- SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'[A-Z]{2}') ;

대문자가 연속적으로 3글자 이상오는 경우 출력

- SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'[A-Z]{3}') ;

사용 예 4.)

시작 문자 지정 ^(캐럿) , 끝나는 문자 지정 $(달러)

시작을 대문자나 소문자로 하는 행 출력

- SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'^[a-zA-Z]') ;

시작을 숫자나 대문자로 시작하는 행 출력

- SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'^[0-9A-Z]') ;

여러가지 조건을 줄 경우 바 기호(|)  를 사용하여 연결 할 수도 있음

소문자로 시작하거나, 숫자로 시작하는 경우

- SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'^[a-z] | ^[0-9]') ; 

STUDENT 테이블에서 학생의 ID중 첫 글자가 s(소문자) 로 시작하고 두번째 글자가 a 나 t가 나오는 id 출력

- SELECT name, id FROM STUDENT WHERE REGEXP_LIKE( id,'^s(a|t).') ;

소문자로 끝나는 모든 행 출력

- SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'[a-z]$') ;

^(캐럿)문자가 대괄호 안에 들어갈 경우에는 대괄호 안의 문자가 아닌 다른 것만 출력하라는 의미

소문자로 시작하지 않는 행을 모두 출력

- SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'^[^a-z]') ;

STUDENT 테이블에서 학생의 id를 조사해 4번째 자리에 v(소문자) 가 있는 행을 출력

- SELECT name, id FROM STUDENT WHERE REGEXP_LIKE( id,'^...v.') ;

특정 조건을 제외한 결과 출력  (NOT)

영문자(대소문자)를 포함하지 않는 행을 출력

- SELECT * FROM reg_test WHERE NOT REGEXP_LIKE( text,'[A-Za-z]') ;

특수문자 찾기 

( '*' 나 '?' 같은 기호는 SQL에서 '모든것' 이라는 뜻을 가진 메타캐릭터 문자이기 때문에 \붙여줘야 한다.)

?가 들어간 행 출력

- SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'\?') ;

?가 들어가지 않는 행 출력

- SELECT * FROM reg_test WHERE NOT REGEXP_LIKE( text,'\?') ;

소문자가 들어 있는 모든 행을 출력 (찾고자 하는 쿼리의 앞에 '*' 나 '?' 를 사용 뒤에 쓰면 적용X)

- SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'*[a-z]') ;

- SELECT * FROM reg_test WHERE REGEXP_LIKE( text,'?[a-z]') ;

2. REGEXP_REPLACE 함수

REPLACE 함수의 확장 개념으로 주어진 문자열에서 특정 패턴을 찾아서 다른 모양으로 치환하는 함수

사용 예1.) 모든 숫자를 특수 기호로 변경

숫자 부분을 '@' 기호로 변경

- SELECT text , REGEXP_REPLACE( text,'[[:digit:]]','@')  FROM reg_test ;

사용 예2.) 특정 패턴을 찾아서 패턴을 변경

숫자를 찾아서 숫자 뒤에 '-*' 을 추가

- SELECT text , REGEXP_REPLACE( text,'[0-9]','\1-*')  FROM reg_test ;

reg_test2 테이블에서 ip의 .(dot) 부분을 모두 삭제하고 출력

- SELECT no, ip, REGEXP_REPLACE(ip,'\.','') FROM reg_test2 ;

reg_test2 테이블에서 ip의 첫번째 .(dot) 부분을 '/' (슬래쉬) 기호로 변경해서 출력

- SELECT no, ip, REGEXP_REPLACE(ip,'\.','/',1,1) FROM reg_test2 ;

사용 예3.) 사용자에게 입력 받은 문자가운데 공백이 여러 개 들어있을 경우 그 공백을 제거 하는 방법 (중요)

사용자가 ID를 'abc 123' 이렇게 입력했을 경우 'abc' 와 '123' 사이의 공백을 없애고 싶은 경우

- SELECT REGEXP_REPLACE('abc  123','( ){1,}','') FROM dual ;

위 예제에서 {1,} 부분을 {1}로 해도 무방

{} 내의 숫자는 앞문자가 나타나는 횟수 또는 범위를 의미

예를 들어 a{5} 'a' 의 5번 반복인 aaaaa

              a{3,} 는 'a'가 3번 이상 반복인 aaa , aaaa , aaaaa ... 등을 의미

              a{3,5} 는 aaa , aaaa , aaaaa 를 의미

              ab{2,3} 은 뒤의 b가 2번, 3번 반복된 형태로 abb , abbb 를 의미

              {,}를 붙이면 이상을 의미

               

사용 예4.) 사용자가 검색어를 입력할 때 공백 문자를 가장 먼저 입력하고 아이디 중간에도 공백이 있어서 모든 공백을 제거하는 예

아이디 입력시 : (공백)  75   true 를 입력하였을때 중간 중간 공백을 모두 제거하는 방법

- SELECT studno,name,id FROM student WHERE id=REGEXP_REPLACE('&id','( ){1,}','') ;

 Enter Value for id :         75       true

3. REGEXP_INSTR 함수

특정 패턴이 출현하는 첫 위치 값을 반환하는 함수

사용 예1.) 특정 문자의 위치를 찾는 방법

text 중에서 '*' 의 위치를 찾는 방법

- SELECT text, REGEXP_INSTR(text,'\*') FROM reg_test ;

4. REGEXP_SUBSTR 함수

SUBSTR 함수의 확장판으로 특정 패턴에서 주어진 문자를 추출해 내는 함수

'abc* *def %ghi,jkl' 이란 문자열에서 첫 글자가 공백이 아니고 ('[^ ]') 그 후에 'def'가 나오는 부분을 추출

- SELECT REGEXP_SUBSTR('abc* *def %ghi,jkl' , '[^ ]+[def]') FROM dual ;

5. REGEXP_COUNT 함수

특정 문자의 개수를 세는 함수

주어진 문자열에서 소문자 'a'가 몇개인지 찾아 주는 예

- SELECT text, REGEXP_COUNT(text,'a') FROM reg_test ;