//MSSQL 정규식 표현

문자의 범위에 포함되는 값으로 검색이 필요할때가 종종 있다.

이럴 때, 정규식 표현이 활용이 되는데 이번 기회에 완벽하게 숙지해본다.

 

1. 영문 검색
 - '[a-zA-Z]%'
 - '[^a-zA-Z]%'

2. 숫자 검색
 - '[0-9]%'
 - '[^0-9]%'

 

3. 한글 검색
 - '[가-힣]'

 

4. 두가지 문자 중 한가지라도 있는 값 검색
 - '%[ea]%'

5. 예약어인 '[' 값 검색
 - '%[[]%'

6. 특수문자를 포함하고 있는 값 검색
 - '%[^0-9a-zA-Z가-힣]%'

 

 

-- 1.1 첫글자가 영어(소문자/대문자)로 시작되는 문자 검색
WITH WITH_TEST AS
( SELECT 'Java'   AS LANGUAGE UNION ALL
  SELECT 'Oracle' AS LANGUAGE UNION ALL
  SELECT 'MS-SQL' AS LANGUAGE UNION ALL
  SELECT '#Net'   AS LANGUAGE UNION ALL
  SELECT 'C#'     AS LANGUAGE UNION ALL
  SELECT 'C++'    AS LANGUAGE 
)
SELECT *
  FROM WITH_TEST
 WHERE LANGUAGE LIKE '[a-zA-Z]%'
 ;


-- 1.2 첫글자가 영어(소문자/대문자)로 시작되지 않는 문자 검색
WITH WITH_TEST AS
( SELECT 'Java'   AS LANGUAGE UNION ALL
  SELECT 'Oracle' AS LANGUAGE UNION ALL
  SELECT 'MS-SQL' AS LANGUAGE UNION ALL
  SELECT '#Net'   AS LANGUAGE UNION ALL
  SELECT 'C#'     AS LANGUAGE UNION ALL
  SELECT 'C++'    AS LANGUAGE 
)
SELECT *
  FROM WITH_TEST
 WHERE LANGUAGE LIKE '[^a-zA-Z]%'
 ;


-- 2.1 첫글자가 숫자로 시작되는 값 검색
WITH WITH_TEST AS
( SELECT 'Java'   AS LANGUAGE UNION ALL
  SELECT 'ORACLE' AS LANGUAGE UNION ALL
  SELECT 'MS-SQL' AS LANGUAGE UNION ALL
  SELECT '#Net'   AS LANGUAGE UNION ALL
  SELECT 'C#'     AS LANGUAGE UNION ALL
  SELECT 'C++'    AS LANGUAGE UNION ALL
  SELECT '9i ORACLE'
)
SELECT *
  FROM WITH_TEST
 WHERE LANGUAGE LIKE '[0-9]%'
 ;


-- 3.1 한글 포함
WITH WITH_TEST AS
( SELECT 'Java'      AS LANGUAGE UNION ALL
  SELECT 'ORACLE'    AS LANGUAGE UNION ALL
  SELECT 'MS-SQL'    AS LANGUAGE UNION ALL
  SELECT '#Net'      AS LANGUAGE UNION ALL
  SELECT 'C#'        AS LANGUAGE UNION ALL
  SELECT 'C++'       AS LANGUAGE UNION ALL
  SELECT '9i ORACLE' AS LANGUAGE UNION ALL
  SELECT '한글 버전'
)
SELECT *
  FROM WITH_TEST
 WHERE LANGUAGE LIKE '%[EA]%' 
 ;


-- 4.1 두가지 문자 중 하나라도 검색되는 경우
WITH WITH_TEST AS
( SELECT 'Java'      AS LANGUAGE UNION ALL
  SELECT 'ORACLE'    AS LANGUAGE UNION ALL
  SELECT 'MS-SQL'    AS LANGUAGE UNION ALL
  SELECT '#Net'      AS LANGUAGE UNION ALL
  SELECT 'C#'        AS LANGUAGE UNION ALL
  SELECT 'C++'       AS LANGUAGE UNION ALL
  SELECT '9i ORACLE' AS LANGUAGE UNION ALL
  SELECT '한글 버전'
)
SELECT *
  FROM WITH_TEST
 WHERE LANGUAGE LIKE '%[EA]%' 
 ;


-- 5.1 예약어인 '[' 값 검색
WITH WITH_TEST AS
( SELECT 'Java'      AS LANGUAGE UNION ALL
  SELECT 'ORACLE'    AS LANGUAGE UNION ALL
  SELECT 'MS-SQL'    AS LANGUAGE UNION ALL
  SELECT '#Net'      AS LANGUAGE UNION ALL
  SELECT 'C#'        AS LANGUAGE UNION ALL
  SELECT 'C++'       AS LANGUAGE UNION ALL
  SELECT '9i ORACLE' AS LANGUAGE UNION ALL
  SELECT '한글 버전[HWP]'
)
SELECT *
  FROM WITH_TEST
 WHERE LANGUAGE LIKE '%[[]%'
 ;


-- 6.1 예약어인 '[' 값 검색
WITH WITH_TEST AS
( SELECT 'Java'      AS LANGUAGE UNION ALL
  SELECT 'ORACLE'    AS LANGUAGE UNION ALL
  SELECT 'MS-SQL'    AS LANGUAGE UNION ALL
  SELECT '#Net'      AS LANGUAGE UNION ALL
  SELECT 'C#'        AS LANGUAGE UNION ALL
  SELECT 'C++'       AS LANGUAGE UNION ALL
  SELECT '9i ORACLE' AS LANGUAGE UNION ALL
  SELECT '한글 버전[HWP]'
)
SELECT *
  FROM WITH_TEST
 WHERE LANGUAGE LIKE '%[^0-9a-zA-Z가-힣]%'
 ;


-- 7.1 특수문자 값 포함 여부 검색
DECLARE @String varchar(1000);
SET @String = 'string';
 
IF @String Like '%[^-_.a-z0-9]%'
    PRINT '특수문자가 있음';
ELSE
    PRINT '특수문자가 없음';

※ 참고
http://docko.tistory.com/entry/MSSQL-Like-문에-정규식-사용하기?category=555744
http://solskjaer.tistory.com/155