REGEXP 3

To get decimals points and vicevers


SELECT REGEXP_REPLACE('18.01', '(\d+)\.(\d+)', '\1') FROM dual;

SELECT REGEXP_REPLACE('18.0991', '(\d+)\.(\d+)', '\2') FROM dual;


For Credit-Card Number System

SELECT regexp_replace( :c, '[[:digit:]]{13,16}', 'X' ) r
FROM dual;

To get decimals points

select (18.9009-floor(18.9009))*power(10,length((18.9009-floor(18.9009)))-1) f from dual

or

SELECT REGEXP_REPLACE('18.9009', '(\d+)\.(\d+)', '\2') FROM dual;


Convert a name 'first middle last' into the 'last middle first' format


SELECT REGEXP_REPLACE('Hubert Horatio Hornblower','(.*) (.*) (.*)','\3 \2 \1') "Reformatted Name" FROM dual ;

To remove dollar sign

SELECT REGEXP_REPLACE('$1,234.56','\$',' ') FROM dual;


SELECT REGEXP_REPLACE('This is a test','t.+','XYZ') FROM dual;

SELECT REGEXP_REPLACE('Mississippi', 'si', 'SI', 1, 0, 'i') FROM dual;

ORA-00932: inconsistent datatypes

ORA-00932: inconsistent datatypes: expected - got CLOB
WHY THIS ERROR OCCURRING ?

WHEN WE EXECUTE THIS QUERY IT RESULTS TO AN ERROR (IE : ORA-00932)

SELECT TO_CLOB('DUMMY') FROM DUAL
UNION
SELECT TO_CLOB('DATA') FROM DUAL;

THIS ERROR CAN BE RESOLVED BY USING UNION ALL

EXAMPLE

SELECT TO_CLOB('DUMMY') FROM DUAL
UNION ALL
SELECT TO_CLOB('DATA') FROM DUAL;