정규표현식

Oracle/SQL

정규표현식

psys 2020. 6. 10. 09:25

728x90

정규 표현식

정규표현식(正規表現式, Regular Expression)은 문자열을 처리하는 방법 중의 하나로
특정한 조건의 문자를 '검색'하거나 '치환'하는 과정을 매우 간편하게 처리 할 수 있도록 하는 수

ORACLE 정규식 (Reqular Expression)
Oracle 10g에서는 REGEXP_로 시작하는 함수를 지원합니다.

강력한 Text 분석 도구로써 LIKE 의 한계를 극복
유닉스의 정규식과 같음.
Pattern-Matching-Rule

함수와 표현식 패턴 (Regular Expression Pattern)을 이용하여 원하는 값을 얻는다.
표현식 패턴에 사용하는 패턴을 만들기 위한 기호

1.정규식 기본 Syntax

   * 정규식은 언제 사용 ?  ETL/전환/이행, Data Mining, Data Cleansing, 데이터 검증 ....
   * 중복, 공백, 데이터 검증, 패턴 체크 ...

   .(dot) 은 모든 문자와 match된다.

정규식 함수

REGEXP_LIKE    : like 연산과 유사하며 정규식 패턴을 검색
REGEXP_REPLACE : 정규식 패턴을 검색하여 대체 문자열로 변경
REGEXP_INSTR   : 정규식 패턴을 검색하여 위치를 반환
REGEXP_SUBSTR  : 정규식 패턴을 검색하여 부분 문자 추출
REGEXP_COUNT   : 정규식 패턴을 검새갛여 발견된 횟수 반환

REGEXP_LIKE(srcstr, pattern [,match_option])

LIKE 연산자와 유사하면, 표현식 패턴 (Regular Expression Pattern)을 수행하여, 일치하는 값을 반환

where절에 들어간다.srcstr : 소스 문자열, 검색하고자 하는 값.
pattern : Regular Expression Operator를 통해 문자열에서
특정 문자를 보다 다양한 pattern으로 검색하는 것이 가능.
match_option : match를 시도할 때의 옵션.

1) 하나숫자가 아닌 문자열을 찾자

WITH V_TEST AS (
  SELECT 'ABCD1234' AS C1 FROM DUAL 
  UNION ALL
  SELECT '1234ABCD' AS C1 FROM DUAL 
  UNION ALL
  SELECT '12345678' AS C1 FROM DUAL
)
SELECT C1
  FROM V_TEST
 WHERE REGEXP_LIKE(C1, '[^[:digit:]]');

WHERE REGEXP_LIKE(C1, '[^[:digit:]]');

digit는 숫자. 숫자가 아닌 것들을 찾는다!

2) 하나라도 숫자가 아닌 것이 있으면 선택, REGEXP_LIKE(ID, '[^[:digit:]]')

  WITH base AS (
  SELECT 'aA11' AS id FROM DUAL 
  UNION ALL
  SELECT '11w211e' AS id FROM DUAL 
  UNION ALL
  SELECT '1qwe#' AS id FROM DUAL
  UNION ALL
  SELECT 'e*' AS id FROM DUAL
  UNION ALL
  SELECT '@!' AS id FROM DUAL
  UNION ALL
  SELECT '123' AS id FROM DUAL
)
SELECT id
  FROM base
 WHERE REGEXP_LIKE(id, '[^[:digit:]]');

3) REGEXP_LIKE(ID, '[[:digit:]]')

  WITH base AS (
  SELECT 'aA11' AS id FROM DUAL 
  UNION ALL
  SELECT '11w211e' AS id FROM DUAL 
  UNION ALL
  SELECT '1qwe#' AS id FROM DUAL
  UNION ALL
  SELECT 'e*' AS id FROM DUAL
  UNION ALL
  SELECT '@!' AS id FROM DUAL
  UNION ALL
  SELECT '123' AS id FROM DUAL
)
SELECT id
  FROM base
 WHERE REGEXP_LIKE(id, '[[:digit:]]');

4) ss 다음 p를 포함하지 않는 것

with v_test as(
    select 'ssa' as c1 from dual union all
    select 'ssp' as c1 from dual union all
    select 'asss' as c1 from dual
)
select c1
from v_test
where regexp_like(c1, 'ss[^p]');

5) ss다음 p나 s를 포함하지 않는 것

with v_test as(
    select 'ssa' as c1 from dual union all
    select 'ssp' as c1 from dual union all
    select 'asss' as c1 from dual
)
select c1
from v_test
where regexp_like(c1, 'ss[^ps]');

2020/06/10 - [Oracle/SQL] - 한정자

한정자

한정자 앞의 문자(그룹, 식) 제한 * : 0개 이상 + : 1개 이상 ? : 0,1 개 {n} : n번 {n,} : n번 이상 {n,m} : n번 이상 m번이하 한정자 : Greed..

yeon960.tistory.com

위의 글 보면서 하세요~!!

6) 임의의 한 문자 [n,m]

SELECT  MEM_NAME
FROM   MEMBER
WHERE  REGEXP_LIKE (MEM_NAME, '이[쁜진]');

ex>

1) [a-z], [0-9]는 소문자 전체와 0부터9까지의 숫자를 나타낸다.

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

2) [a-z]와[0-9]사이에 공백이 있는 것이 보이시죠? 이렇게 공백도 구분값으로 사용할 수 있다.

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

3) [a-z]?[0-9]와 [a-z]*[0-9] 이 뜻은 공백이 여러개 포함한다는 뜻

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

4) 숫자를포함한 모든 문자를 select

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

5) 소문자 영어 를 포함한 모든 문자를 select

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

6) 대문자 영어가 연속으로 3자리 있는 행을 출력

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

7) 숫자가 연속으로 3자리 있는 행을 출력

SELECT * FROM EX12_5 WHERE REGEXP_LIKE(text, '[0-9]{3}');

8) 영어 대문자, 숫자 대문자 모두 3개 이상

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

9) 숫자로 시작되는 행

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

10) 소문자로끝나는행

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

11) 숫자로 시작하지 않는 행

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

12) ‘A’나 1을 포함하고 있는행

SELECT * FROM EX12_5 WHERE REGEXP_LIKE(text, 'A|1');

13) '?' 문자가 들어가는행

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

14) 한글만

WITH BASE AS (
    SELECT 'aA11'   AS ID FROM DUAL UNION ALL
    SELECT '1qw211e' AS ID FROM DUAL UNION ALL
    SELECT '1qwe#'  AS ID FROM DUAL UNION ALL
    SELECT 'e*'     AS ID FROM DUAL UNION ALL
    SELECT '@!'     AS ID FROM DUAL UNION ALL
    SELECT '123'    AS ID FROM DUAL UNION ALL
    SELECT 'aa최a'    AS ID FROM DUAL UNION ALL
    SELECT '유신'    AS ID FROM DUAL
)
SELECT ID
  FROM BASE
 WHERE REGEXP_LIKE(id, '[가-힝]');

REGEXP_SUBSTR(srcstr, pattern, [,position[,occurrence[,match_option]]])

SUBSTR 함수의 기능을 확장함. 주어진 문자열을 대상으로 정규 표현식 패턴을 수행하여, 일치하는 하위 문자열을 반환

문법 : REGEXP_SUBSTR(srcstr, pattern, [,position[,occurrence[,match_option]]])

srcstr : 소스 문자열
position : Oracle이 문자열에서 특정 문자를 어디에서 찾아야 하는지 위치를 나타냄. 기본으로 1로 설정되어 있으므로, 문자열의 처음부터 검색을 시작.
occurrence : 검색하고자 하는 문자열에서 특정 문자의 발생 횟수. 기본으로 1로 설정되어 있으며, 이는 Oracle이 문자열에서 첫번째 발생 pattern을 찾는다는 의미.
match_option : match를 시도할 때의 옵션

SELECT REGEXP_SUBSTR(MEM_MAIL, '[^@]+', 1, 1) AS "ID"
     , REGEXP_SUBSTR(MEM_MAIL, '[^@]+', 1, 2) AS "MailAddr"
     , MEM_MAIL
FROM MEMBER;

SELECT MEM_NAME, MEM_MAIL, REGEXP_SUBSTR(MEM_MAIL, '[^@]+') AS SUBEMAIL
FROM MEMBER;

사용방법
REGEXP_SUBSTR(대상 문자, 패턴, 시작 위치(최소값1),매칭순번)
REGEXP_SUBSTR('C-01-02','[^-]+',1,1)
결과 = C
REGEXP_SUBSTR('C-01-02','[^-]+',1,2)
결과 = 01
REGEXP_SUBSTR('C-01-02','[^-]+',1,3)
결과 = 02

대괄호 [] 안의 ^ 는 NOT의 의미를 나타냄

^ 문자가 대괄호 밖에서 사용되면 문자열의 시작을 의미함
+ 는 문자패턴이 1개이상 연결될 때를 나타냄, 위 예제에서 01,02등 2개이상 나타내기 위함
+ 시작위치 & 매칭 순번

앞의 대상문자와 패턴에 의해 나누어진 문자들을 몇번째 INDEX에서 시작하여 몇번째의 나누어진 문자를 가져올것인지에 대한 PARAMETER
위 예제에서 (1,2)는 C // 01 // 02 의 나누어진 문자중 1번째 INDEX부터 시작하는 2번째 문자를 가져오라는 뜻

1) 형님

SELECT REGEXP_SUBSTR('아빠_엄마_형님_동생', '[^_]+', 1,3) from dual ;

-> _로 시작하지 않는 것을 1번째부터 검색해서 3번째로 나온 단어!
2) 1

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', '[0-9]')  
-- SELECT REGEXP_SUBSTR('LEE apgil ok 12345', '[0-9]',1,1)과 같은 의미!!
FROM DUAL;

[0-9]는 숫자

3) L

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', '[^0-9]')  
FROM DUAL;

숫자가 아닌 것 하나

4) LEE apgil ok

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', '[^0-9]+')  
FROM DUAL;

숫자가 아닌거까지 1개 이상

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', '[ ^0-9]+')  
FROM DUAL;

6) 12345

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', ' [ ^0-9]+')  
FROM DUAL;

7) apgil ok

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', ' [^0-9]+')  
FROM DUAL;

8) apgil ok 12345

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', '[ ^0-9].+')  
FROM DUAL;

9) LEE apgil ok 12345

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', '[^0-9].+')  
FROM DUAL;

10) E apgil ok 12345

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', '[^0-9] .+')  
FROM DUAL;

11) ok

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', ' [^0-9]. +')  
FROM DUAL;

12) apgil ok 1

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', ' [^0-9]+.')  
FROM DUAL;

13) apgil ok 12

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', ' [^0-9]+..')  
FROM DUAL;

14) apgi

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', ' [^ ]{4}')  
FROM DUAL;

15) 1

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', '[[:digit:]]')  
FROM DUAL;

16) 12345

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', '[[:digit:]]+')  
FROM DUAL;

17) 123

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', '[[:digit:]]..')  
FROM DUAL;

18) L

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', '[[:alpha:]]')  
FROM DUAL;

19) a

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', ' [[:alpha:]]')  
FROM DUAL;

20) apg

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', ' [[:alpha:]]{3}')  
FROM DUAL;

21) LEE

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', '[^ ]+',1,1)  
FROM DUAL;

22) apgil

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', '[^ ]+',1,2)  
FROM DUAL;

23) ok

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', '[^ ]+',1,3)  
FROM DUAL;

24) 12345

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', '[^ ]+',1,4)  
FROM DUAL;

25) apgil

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', ' [^ ]+',1,1)  
FROM DUAL;

26) LEE apgil ok

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', '[^[:digit:]]+')  
FROM DUAL;

숫자가 아닌 것들

27) apgil ok

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', '[ ][^0-9]+') 
FROM DUAL;

공백 찾고 시작되는거 전부!

28) apgi

SELECT REGEXP_SUBSTR('LEE apgil ok 12345', '[a-z]{2}+')
FROM DUAL;

RREGEXP_REPLACE

주어진 문자열을 대상으로 정규 표현식 패턴을 조사하여, 다른 문자로 대체한다.

문법 : REGEXP_REPLACE(srcstr, pattern [,replacestr[,position[,occurrence[,match_option]]]])

replacestr : 대체하고자 하는 문자열을 나타냄

with v_test as(
    select '7907051234567' ssn from dual
    union
    select '790705' ssn from dual
    union
    select '790705aawfdd' ssn from dual
    union
    select '790705aa56dd' ssn from dual
)
select regexp_replace(ssn, '[0-9]', '*', 7) AS "SSN"
FROM V_TEST;

순서가 이상하게 출력되는 이유는 정렬의 문제 때문!!

7번째 수부터 *로

1) 둘 이상의 공백 문자를 하나로 대체

SELECT REGEXP_REPLACE('Oracle is the   Information Company', '( ){2,}', ' ') AS "Result"
    , REGEXP_REPLACE('Oracle is the   Information Company', '( ){2,}', '-') AS "Result2"
FROM dual;

{2,} 2개 이상의 공백!! 공백이 10개여도 ' '이나 '-'로 대체된다.

2) 전화번호의 표현 방식을 3자리, 3자리, 4자리로 묶음

SELECT REGEXP_REPLACE('555.123.4567','([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})','(\1) \2 - \3') AS "Res1"
    , REGEXP_REPLACE('555.123.4567','([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})','(\1) (\2) - \3') AS "Res2"
FROM dual;

REGEXP_INSTR

정규 표현을 만족하는 부분의 위치를 반환

문법 : REGEXP_INSTR(srcstr, pattern [,position[,occurrence[,returnparam[,match_option]]]])

position : 검색 시작 위치
occurrence : 발생 횟수.
returnparam : 반환 옵션.
match_option : match를 시도할 때의 옵션.

SELECT REGEXP_INSTR('Regular Expression', 'a') AS "REGEXP_INSTR"
FROM dual;

ex>

employees 사원명에서 'ap'또는 'gi'가 있는 사람만 검색

select emp_name
from employees
where REGEXP_LIKE(emp_name,'ap|gi');

ex>

employees 전화번호에서

xxx.xxx. 패턴이 아닌 것을 조회

select phone_number
from employees
where not REGEXP_LIKE(phone_number,'[0-9]{3}\.[0-9]{3}\.');

'Oracle > SQL' 카테고리의 다른 글

기본 QUERY - DDL (0)	2022.09.26
한정자 (0)	2020.06.10
내장함수 (0)	2020.06.05
표현식과 조건식 (1)	2020.06.03
DDL(Data Define Language, 데이터정의어) (0)	2020.06.03

현재글정규표현식

2875, jqtree, 이클립스기본세팅, ignored, jqtreetable, Settings, 하이픈, DatePicker, unescapeHtml3, 그리디알고리즘, jstree, treetable, 트리테이블, escapeXml, utf8설정, 백준, StringEscapeUtils, jstreetable, 200602 #백준 #java #그리디알고리즘, 백준2875,

Today :
Yesterday :

성연이의 프로그래밍 블로그

정규표현식

정규 표현식

REGEXP_LIKE(srcstr, pattern [,match_option])

REGEXP_SUBSTR(srcstr, pattern, [,position[,occurrence[,match_option]]])

RREGEXP_REPLACE

REGEXP_INSTR

'Oracle > SQL' 카테고리의 다른 글

'Oracle/SQL'의 다른글

티스토리툴바

« 2024/11 »
일	월	화	수	목	금	토
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30

정규표현식

정규 표현식

REGEXP_LIKE(srcstr, pattern [,match_option])

REGEXP_SUBSTR(srcstr, pattern, [,position[,occurrence[,match_option]]])

RREGEXP_REPLACE

REGEXP_INSTR

'Oracle > SQL' 카테고리의 다른 글

'Oracle/SQL'의 다른글

관련글

티스토리툴바