Useful Oracle SQL Regexp Snippets

sql_regexp_samples.sql

--Split a string on :

-- Second Element: 
select regexp_substr('First String: Second String', '[^:]+', 1, 2)
from dual;

-- Valid IP address
select 1 from dual
where regexp_like('192.168.2.254',  '^(([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\.){3}([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])$');'

-- valid subnet
AND LINE_VARCHAR_3 IS NOT NULL AND regexp_like(line_varchar_3,  '^[1-2]{1}[2,4,5,9]{1}[0,2,4,5,8]{1}\.[0-2]{1}[0,2,4,5,9]{1}[0,2,4,5,8]{1}\.[0-2]{1}[0,2,4,5,9]{1}[0,2,4,5,8]{1}\.[0-9]{1,3}$')


-- Valid Serial Number for the Big O

(
  regexp_like (line_varchar,'^[0-9]{4}(AK|FM)[A-Z0-9]{4}$')
  or regexp_like (line_varchar, '^AK\d{8}$')
  or regexp_like (line_varchar, 'FX\d{6}[A-Z[0-9]{2}$')
)

-- UUID:
-- http://en.wikipedia.org/wiki/Uuid#Definition


regexp_like(line_varchar_2,  '^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-4[0-9a-fA-F]{3}-(:?8|9|A|B|a|b)[0-9a-fA-F]{3}-[0-9a-fA-F]{12}$')

-- or

regexp_like(line_varchar_2,  '^[[:xdigit:]]{8}-[[:xdigit:]]{4}-4[[:xdigit:]]{3}-(:?8|9|A|B|a|b)[[:xdigit:]]{3}-[[:xdigit:]]{12}$')


-- Find first word (up to space or end of string):

select 
  customer_name, 
  CASE WHEN instr(customer_name, ' ') = 0 THEN customer_name
       ELSE substr(customer_name, 1, instr(customer_name, ' ')-1)
END as first_word,
regexp_replace(customer_name, '(^[[:graph:]]+)([[:space:]].*)', '\1') as better_first_word
from cust_tb