정규식 함수

ID | NAME   | PHONE         | EMAIL
---+--------+---------------+------------------
1  | 김하나 | 010-1234-5678 | [email protected]
2  | 이둘   | 01012345678   | [email protected]
3  | 박셋   | 010-123-4567  | [email protected]
4  | 최넷   | 02-345-6789   | [email protected]
5  | 정다섯 | 010-12345-678 | five@example
6  | 한여섯 | 010 9876 5432 | six@@example.com
7  | 오일곱 | 010-5555-6666 | [email protected]
8  | 윤여덟 | NULL          | [email protected]
9  | 강아홉 | abc-defg-hijk | nine.example.com
(9행)

SELECT
       id
     , name
     , phone
  FROM contact
 WHERE REGEXP_LIKE(phone, '^01[016789]-?[0-9]{3,4}-?[0-9]{4}
  

)
 ORDER BY id;

ID | NAME   | PHONE
---+--------+--------------
1  | 김하나 | 010-1234-5678
2  | 이둘   | 01012345678
3  | 박셋   | 010-123-4567
7  | 오일곱 | 010-5555-6666
(4행)

SELECT
       id
     , name
     , phone
  FROM contact
 WHERE NOT REGEXP_LIKE(phone, '^01[016789]-?[0-9]{3,4}-?[0-9]{4}
  

)
 ORDER BY id;

ID | NAME   | PHONE
---+--------+--------------
4  | 최넷   | 02-345-6789
5  | 정다섯 | 010-12345-678
6  | 한여섯 | 010 9876 5432
9  | 강아홉 | abc-defg-hijk
(4행)

SELECT
       id
     , email
     , REGEXP_LIKE(email, '^[A-Za-z0-9._-]+@[A-Za-z0-9-]+(\.[A-Za-z0-9-]+)+
  

) AS ok
  FROM contact
 ORDER BY id;

SELECT
       id
     , phone
     , phone LIKE '___-____-____' AS by_like
     , REGEXP_LIKE(phone, '^[0-9]{3}-[0-9]{4}-[0-9]{4}
  

) AS by_regexp
  FROM contact
 WHERE id IN (1, 2, 7, 9)
 ORDER BY id;

ID | PHONE         | BY_LIKE | BY_REGEXP
---+---------------+---------+----------
1  | 010-1234-5678 | true    | true
2  | 01012345678   | false   | false
7  | 010-5555-6666 | true    | true
9  | abc-defg-hijk | true    | false
(4행)

SELECT
       id
     , memo
     , REGEXP_LIKE(memo, 'ord-2024') AS plain
     , REGEXP_LIKE(memo, 'ord-2024', 'i') AS ignore_case
  FROM contact
 WHERE id IN (1, 4, 8)
 ORDER BY id;

ID | MEMO                      | PLAIN | IGNORE_CASE
---+---------------------------+-------+------------
1  | 주문 ORD-2024-0015 문의   | false | true
4  | ord-2024-0040 소문자 표기 | true  | true
8  | NULL                      | NULL  | NULL
(3행)