[ORACLE] 특수문자 찾기/특수문자 치환하기/특수문자 정규식

특수문자 정규식

'[[:punct:]]'

 

위의 정규식을 이용하면 찾고, 치환하는 것도 가능하다.

 

 

 

특수문자 찾기

조건절에 REGEXP_LIKE 함수 사용

SELECT [조회할 필드명]
  FROM [조회할 테이블명]
 WHERE REGEXP_LIKE([특수문자를 찾을 필드명], '[[:punct:]]')

사용예제 ▼

WITH TEMP AS (
  SELECT 'as df' AS id FROM DUAL
  UNION ALL
  SELECT 'asdf!' AS id FROM DUAL
  UNION ALL 
  SELECT 'asdf`' AS id FROM DUAL
  UNION ALL
  SELECT 'asdf1234' AS id FROM DUAL
  UNION ALL
  SELECT '1234' AS id FROM DUAL
)
SELECT id
  FROM TEMP
 WHERE REGEXP_LIKE(id, '[[:punct:]]');

결과 ▼

 

 

 

특수문자 치환(제거)

SELECT절에 REGEXP_REPLACE 함수 사용

SELECT REGEXP_REPLACE([특수문자가 있는 필드명], '[[:punct:]]')
  FROM [테이블명]

사용예제 ▼

WITH TEMP AS (
  SELECT 'as df' AS id FROM DUAL
  UNION ALL
  SELECT 'asdf!' AS id FROM DUAL
  UNION ALL 
  SELECT 'asdf`' AS id FROM DUAL
  UNION ALL
  SELECT 'asdf1234' AS id FROM DUAL
  UNION ALL
  SELECT '1234' AS id FROM DUAL
)
SELECT REGEXP_REPLACE(id, '[[:punct:]]')
  FROM TEMP

결과 ▼