REGEXP 1

Find the position of try, trying, tried or tries

SELECT REGEXP_INSTR('We are trying to make the subject easier','tr(y(ing)?|(ied)|(ies))') RESULTNUM FROM dual;

To extract numbers

select regexp_substr('Oracle Database 10g is first grid database','[0-9]+') version from dual;


Following query places a space between Oracle its version

select regexp_replace('Oracle10g','([[:alpha:]])([[:digit:]]+.)','\1 \2') from dual;

Displays the starting position of one or more digits.


select regexp_instr('Oracle Database 10g is first grid aware database','[0-9]+') position from dual;

SELECT REGEXP_replace(TO_CHAR(sysdate, 'YYYY'), '^200[5-8]$','0') FROM dual

SELECT REGEXP_replace(TO_CHAR(sysdate, 'YYYY'), '^200[5-9]$','0') FROM dual


Only retreive data which contains digits
SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:digit:]]');


To Remove Special Characters SELECT REGEXP_REPLACE('##$$$123&&!!__!','[^[:alnum:]'' '']', NULL) FROM dual; SELECT translate('##$$$1$$2#3&&!!__!', '[0-9]#$&&!_','[0-9]') FROM dual; SELECT translate('##$$$123&&!!__!', '0#$&&!_','0') FROM dual; FOR MORE REFERENCE OF REGEXP