Oracle에서 컬럼값에 문자가 있는지 확인하는 방법

Oracle 데이터베이스를 운영하거나 SQL을 작성하다 보면, 특정 컬럼에 숫자가 아닌 문자가 포함되어 있는지 확인해야 할 때가 있습니다. 예를 들어, 주민등록번호, 전화번호, 회원번호 등 숫자만 있어야 하는 필드에 문자가 섞여 있을 경우 이를 필터링하거나 정제하는 작업이 필요합니다.

1. REGEXP_LIKE 함수로 문자 포함 여부 확인

Oracle 10g 이상부터 지원하는 REGEXP_LIKE 함수는 정규표현식을 기반으로 문자열을 검사할 수 있어 매우 강력합니다. 문자가 포함되어 있는지를 확인하려면 다음과 같이 사용합니다.

SELECT *
FROM your_table
WHERE REGEXP_LIKE(your_column, '[A-Za-z]');
  

위 쿼리는 해당 컬럼에 영문 대소문자가 하나라도 포함된 행만 반환합니다. 숫자만 있는 값은 제외됩니다.

2. 숫자가 아닌 문자가 포함된 경우 찾기

만약 컬럼에 숫자 외의 문자가 포함되어 있는지 확인하고 싶다면 다음과 같은 패턴을 사용할 수 있습니다.

SELECT *
FROM your_table
WHERE NOT REGEXP_LIKE(your_column, '^[0-9]+$');
  

이 쿼리는 해당 컬럼값이 숫자로만 구성되지 않은 경우에만 결과를 반환합니다.

3. TRANSLATE 함수로 문자 확인

REGEXP_LIKE를 사용할 수 없는 경우, TRANSLATE 함수를 활용한 우회 방법도 있습니다. 숫자를 모두 공백으로 치환한 결과가 NULL이 아니면, 문자가 포함된 것입니다.

SELECT *
FROM your_table
WHERE TRANSLATE(your_column, '0123456789', '          ') IS NOT NULL;
  

이 방식은 단순하지만 유효하며, 숫자 외의 문자가 포함되었는지 판단하는 데 사용됩니다.

4. INSTR 함수로 특정 문자 포함 여부 확인

특정 문자의 포함 여부만 확인할 때는 INSTR 함수를 사용할 수 있습니다. 예를 들어 'A'가 포함된 데이터를 찾으려면:

SELECT *
FROM your_table
WHERE INSTR(your_column, 'A') > 0;
  

대소문자를 구분하므로, UPPER 함수 또는 LOWER 함수를 함께 사용하는 것이 좋습니다.

5. 정규표현식으로 특수문자, 한글 등 포함 여부 확인

특수문자, 한글 등 영문/숫자가 아닌 문자를 찾으려면 아래와 같이 사용합니다.

SELECT *
FROM your_table
WHERE REGEXP_LIKE(your_column, '[^A-Za-z0-9]');
  

위 쿼리는 영문자 및 숫자를 제외한 문자가 포함된 행을 조회합니다. 한글, 특수문자 등 모든 비영문/숫자 문자가 대상이 됩니다.

6. 실전 예제 테이블로 확인

CREATE TABLE sample_data (
    id NUMBER,
    value VARCHAR2(100)
);

INSERT INTO sample_data VALUES (1, '12345');
INSERT INTO sample_data VALUES (2, 'abc123');
INSERT INTO sample_data VALUES (3, '999');
INSERT INTO sample_data VALUES (4, 'abc');
  

문자가 포함된 행만 조회하려면 다음과 같이 쿼리합니다:

SELECT *
FROM sample_data
WHERE REGEXP_LIKE(value, '[A-Za-z]');
  

결과적으로 ID가 2, 4인 행이 반환되며, 이들은 문자('a', 'b', 'c')가 포함된 값입니다.

7. 마무리 및 활용 팁

Oracle에서 문자 포함 여부를 확인해야 할 때는 REGEXP_LIKE 함수를 사용하는 것이 가장 유연하고 정확한 방법입니다. 다만, 성능 이슈가 있을 수 있으므로 데이터량이 많은 경우에는 인덱스가 적용되는 다른 방법들과 적절히 병행해야 합니다.

또, 정규표현식은 초보자에게는 다소 난해할 수 있으므로, 먼저 간단한 패턴부터 적용해보고 점차 확장해 나가는 것이 좋습니다.