SQL 구문에서 정규 표현식 사용

소개

Oracle 데이터베이스는 SQL 구문에서 복잡한 패턴 매칭과 데이터 추출을 가능하게 하는 강력한 정규 표현식 기능을 제공합니다. 이 가이드는 Oracle 데이터베이스에서 SQL 정규 표현식을 효과적으로 사용하여 데이터 처리 성능을 향상시키고 실무에서 유용하게 활용하는 방법을 상세히 설명합니다. 데이터베이스 관리자(DBA)와 애플리케이션 개발자 모두에게 유용한 정보를 제공하며, 실제 코드 예제와 실행 결과를 통해 이해를 돕습니다.

정규 표현식 연산자

Oracle SQL은 다양한 정규 표현식 연산자를 지원하며, 이를 통해 복잡한 패턴을 정의하고 검색할 수 있습니다. 주요 연산자는 다음과 같습니다.

  • . (Dot): 임의의 단일 문자와 매칭
  • * (Asterisk): 선행 문자의 0회 이상 반복과 매칭
  • + (Plus): 선행 문자의 1회 이상 반복과 매칭
  • ? (Question Mark): 선행 문자의 0회 또는 1회 출현과 매칭
  • [] (Square Brackets): 문자 집합을 정의하여 해당 집합 내의 한 문자와 매칭
  • [^] (Caret in Square Brackets): 문자 집합을 부정하여 해당 집합 내에 없는 한 문자와 매칭
  • () (Parentheses): 하위 표현식을 그룹화
  • | (Pipe): 여러 패턴 중 하나를 선택

Oracle SQL 정규 표현식 함수

Oracle 데이터베이스는 SQL 구문에서 정규 표현식을 사용할 수 있도록 다양한 함수를 제공합니다. 이러한 함수들은 데이터 검색, 유효성 검사, 변환 등 다양한 작업에 유용하게 활용될 수 있습니다.

  • REGEXP_LIKE: 주어진 문자열이 특정 정규 표현식과 일치하는지 여부를 판단합니다.
  • REGEXP_INSTR: 주어진 문자열 내에서 특정 정규 표현식과 일치하는 부분 문자열의 위치를 반환합니다.
  • REGEXP_SUBSTR: 주어진 문자열 내에서 특정 정규 표현식과 일치하는 부분 문자열을 추출합니다.
  • REGEXP_REPLACE: 주어진 문자열 내에서 특정 정규 표현식과 일치하는 부분 문자열을 다른 문자열로 대체합니다.
  • REGEXP_COUNT: 주어진 문자열 내에서 특정 정규 표현식과 일치하는 횟수를 반환합니다.

정규 표현식 예제 및 실무 적용

다음은 Oracle SQL에서 정규 표현식을 사용하는 몇 가지 예제입니다.

1. 전화번호 형식 검사

다음 예제는 contacts 테이블의 phone_number 열에 저장된 전화번호가 특정 형식을 따르는지 검사합니다. 여기서 사용된 형식은 “(XXX) XXX-XXXX”입니다.


  CREATE TABLE contacts (
  contact_id NUMBER PRIMARY KEY,
  phone_number VARCHAR2(20)
  );

  INSERT INTO contacts (contact_id, phone_number) VALUES (1, '(123) 456-7890');
  INSERT INTO contacts (contact_id, phone_number) VALUES (2, '123-456-7890');

  SELECT contact_id, phone_number
  FROM contacts
  WHERE REGEXP_LIKE(phone_number, '^\([0-9]{3}\) [0-9]{3}-[0-9]{4}$');

실행 결과:


  CONTACT_ID PHONE_NUMBER
  ---------- --------------------
  1 (123) 456-7890

2. 이메일 주소 유효성 검사

다음 예제는 employees 테이블의 email 열에 저장된 이메일 주소가 유효한 형식을 따르는지 검사합니다.


  CREATE TABLE employees (
  employee_id NUMBER PRIMARY KEY,
  email VARCHAR2(100)
  );

  INSERT INTO employees (employee_id, email) VALUES (1, 'john.doe@example.com');
  INSERT INTO employees (employee_id, email) VALUES (2, 'invalid-email');

  SELECT employee_id, email
  FROM employees
  WHERE REGEXP_LIKE(email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$');

실행 결과:


  EMPLOYEE_ID EMAIL
  ----------- ------------------------
  1 john.doe@example.com

3. 문자열 내에서 특정 패턴 추출

다음 예제는 products 테이블의 product_name 열에서 특정 패턴을 추출합니다. 여기서 패턴은 “[A-Z]{3}-[0-9]{3}” 형식입니다.


  CREATE TABLE products (
  product_id NUMBER PRIMARY KEY,
  product_name VARCHAR2(100)
  );

  INSERT INTO products (product_id, product_name) VALUES (1, 'ABC-123 Product');
  INSERT INTO products (product_id, product_name) VALUES (2, 'DEF-456 Another Product');

  SELECT product_id, product_name,
  REGEXP_SUBSTR(product_name, '[A-Z]{3}-[0-9]{3}') AS extracted_pattern
  FROM products
  WHERE REGEXP_LIKE(product_name, '[A-Z]{3}-[0-9]{3}');

실행 결과:


  PRODUCT_ID PRODUCT_NAME EXTRACTED_PATTERN
  ---------- -------------------- -------------------
  1 ABC-123 Product ABC-123
  2 DEF-456 Another Product DEF-456

4. 문자열 대체

다음 예제는 addresses 테이블의 street_address 열에서 특정 패턴을 다른 문자열로 대체합니다. 여기서 패턴은 “Ave”를 “Avenue”로 대체합니다.


  CREATE TABLE addresses (
  address_id NUMBER PRIMARY KEY,
  street_address VARCHAR2(200)
  );

  INSERT INTO addresses (address_id, street_address) VALUES (1, '123 Main Ave');
  INSERT INTO addresses (address_id, street_address) VALUES (2, '456 Oak St');

  SELECT address_id, street_address,
  REGEXP_REPLACE(street_address, 'Ave', 'Avenue') AS updated_address
  FROM addresses
  WHERE REGEXP_LIKE(street_address, 'Ave');

실행 결과:


  ADDRESS_ID STREET_ADDRESS UPDATED_ADDRESS
  ---------- ------------------------------ ------------------------------
  1 123 Main Ave 123 Main Avenue

5. 문자열 내 패턴 횟수 계산

다음 예제는 comments 테이블의 comment_text 열에서 특정 패턴이 몇 번 나타나는지 계산합니다. 여기서 패턴은 “SQL”이라는 단어가 몇 번 등장하는지 확인합니다.


  CREATE TABLE comments (
  comment_id NUMBER PRIMARY KEY,
  comment_text VARCHAR2(4000)
  );

  INSERT INTO comments (comment_id, comment_text) VALUES (1, 'This comment contains SQL and another SQL statement.');
  INSERT INTO comments (comment_id, comment_text) VALUES (2, 'No SQL here.');

  SELECT comment_id, comment_text,
  REGEXP_COUNT(comment_text, 'SQL') AS sql_count
  FROM comments;

실행 결과:


  COMMENT_ID COMMENT_TEXT SQL_COUNT
  ---------- ---------------------------------------- ----------
  1 This comment contains SQL and another SQL statement. 2
  2 No SQL here. 0

성능 튜닝 고려 사항

정규 표현식은 강력하지만, 과도하게 사용하면 성능 저하를 초래할 수 있습니다. 복잡한 정규 표현식은 데이터베이스 서버에 상당한 부하를 줄 수 있으므로, 다음과 같은 성능 튜닝 고려 사항을 숙지하는 것이 중요합니다.

  • 정규 표현식 단순화: 가능한 한 간단하고 효율적인 정규 표현식을 사용합니다.
  • 인덱스 활용: 정규 표현식을 사용하는 WHERE 절에 인덱스를 활용할 수 있는지 검토합니다.
  • 테스트 및 프로파일링: 다양한 데이터 볼륨에 대해 정규 표현식의 성능을 테스트하고, 필요한 경우 튜닝합니다.
  • 캐싱 활용: 자주 사용되는 정규 표현식 결과를 캐싱하여 성능을 최적화합니다.

결론

Oracle 데이터베이스에서 제공하는 SQL 정규 표현식은 데이터 처리 및 분석에 매우 유용한 도구입니다. 다양한 함수와 연산자를 활용하여 복잡한 패턴 매칭 및 데이터 변환을 수행할 수 있습니다. 하지만 성능을 고려하여 적절히 사용하는 것이 중요하며, 실무에 적용하기 전에 충분한 테스트와 튜닝을 거쳐야 합니다.

위로 스크롤