Using Regular Expression - REGEXP_SUBSTR example

SELECT REGEXP_SUBSTR('(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl-orcl2.myhost.ca)(PORT=41295))(CONNECT_DATA=(SID=ORCL2)))', '[(]HOST[=][^()]+[)]',1,1,'i') RESULT
FROM dual;


Result:

(HOST=orcl-orcl2.myhost.ca)

Find All Non-Numeric Values in a Column

Return All Non-Numeric Values:

SELECT uen

FROM scott.dept 

WHERE NOT REGEXP_LIKE(uen, '^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$');

Return Non-Integers:

SELECT c1 FROM t1 WHERE NOT REGEXP_LIKE(c1, '^[0-9]+$');