Regular Expression Functions

REGEXP_LIKE(source, pattern)
REGEXP_LIKE(source, pattern, match_param)

-- Query messages containing 'error' or 'warn', ignoring case
SELECT *
  FROM sensor_text
 WHERE REGEXP_LIKE(message, 'error|warn', 'i');

-- Query codes starting with digits
SELECT *
  FROM event_log
 WHERE REGEXP_LIKE(code, '^[0-9]+');

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

REGEXP_INSTR(source, pattern)
REGEXP_INSTR(source, pattern, position)
REGEXP_INSTR(source, pattern, position, occurrence)
REGEXP_INSTR(source, pattern, position, occurrence, return_pos)
REGEXP_INSTR(source, pattern, position, occurrence, return_pos, match_param)

-- Position after the first 'The' match, ignoring case
SELECT REGEXP_INSTR('TechOnTheNet', 'The', 1, 1, 1, 'i');
-- Result: 10 (position after 'The')

REGEXP_SUBSTR(source, pattern)
REGEXP_SUBSTR(source, pattern, position)
REGEXP_SUBSTR(source, pattern, position, occurrence)
REGEXP_SUBSTR(source, pattern, position, occurrence, match_param)

-- Extract the second vowel, ignoring case
SELECT REGEXP_SUBSTR('TechOnTheNet', 'a|e|i|o|u', 1, 2, 'i');
-- Result: 'O'

-- Extract the first octet from an IP address
SELECT REGEXP_SUBSTR(ip_str, '[0-9]+', 1, 1) FROM log_table;

-- Extract an error code from a log
SELECT REGEXP_SUBSTR(message, 'ERR-[0-9]+') FROM event_log;

REGEXP_REPLACE(source, pattern)
REGEXP_REPLACE(source, pattern, replacement)
REGEXP_REPLACE(source, pattern, replacement, position)
REGEXP_REPLACE(source, pattern, replacement, position, occurrence)
REGEXP_REPLACE(source, pattern, replacement, position, occurrence, match_param)

-- Replace the second vowel with 'Z', ignoring case
SELECT REGEXP_REPLACE('TechOnTheNet', 'a|e|i|o|u', 'Z', 1, 2, 'i');
-- Result: 'TechZnTheNet'

-- Remove all digits
SELECT REGEXP_REPLACE(code, '[0-9]', '') FROM log_table;

-- Normalize consecutive whitespace to one space
SELECT REGEXP_REPLACE(message, '\s+', ' ') FROM event_log;