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;

REGEXP 2

Contains alphabets

SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:alpha:]]')

Contains only alphabets

SELECT * FROM test WHERE REGEXP_LIKE(testcol, '^[[:alpha:]].*[[:alpha:]]$')

Contains enter character

SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:cntrl:]]{1}')

OR

SELECT * FROM test WHERE testcol like '%'||chr(13)||'%'
-------------------------
Start with alphabets

SELECT * FROM test WHERE REGEXP_LIKE(testcol, '^[[:alpha:]]')

Ends with alphabets

SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:alpha:]]$')

Contains alphabets only with 5 and more characters

SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:alpha:]]{5}')

Contains punctuations

SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:punct:]]')

Contains space

SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:space:]]')



To insert a space between the characters

SELECT testcol, REGEXP_REPLACE(testcol, '(.)', '\1 ') RES FROM test
To insert a hypen between every 4  characters
 SELECT testcol, REGEXP_REPLACE(testcol, '(....)', '\1-') RES FROM test

To find third charactes with 'a'

SELECT testcol FROM test WHERE REGEXP_LIKE(testcol, '^..a.');

OR

SELECT testcol FROM test WHERE testcol LIKE '__a%'

To find the field having continous 3 spaces

SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:space:]]{3}');

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

REGEXP

REGEXP_LIKE

SELECT * FROM scott.emp WHERE REGEXP_LIKE(ENAME,'FR') ;
OR
SELECT * FROM scott.emp WHERE ENAME LIKE '%FR%'

-------------------

SELECT testcol FROM test WHERE REGEXP_LIKE(testcol, '^Ste(v|ph)en$');
OR
SELECT testcol FROM test WHERE testcol like 'Steven%' or testcol like 'Stephen%'



parameters can be a combination of (Match Options)
* i: to match case insensitively
* c: to match case sensitively
* n: to make the dot (.) match new lines as well
* m: to make ^ and $ (Anchoring Characters) match beginning and end of a line in a multiline string

If you want all the entries that start with S, search for ^s

End with R, use r$

Start with S and end with H, use ^s.*h$

Start with S or end with R, use ^s|r$

All 4 letter names, use ^….$

Contains B, C, D or K, use [b-d,k]

All names with double letters, use (.)\1

Posix Characters
[:alnum:] Alphanumeric characters
[:alpha:] Alphabetic characters
[:blank:] Blank Space Characters
[:cntrl:] Control characters (nonprinting)
[:digit:] Numeric digits
[:graph:] Any [:punct:], [:upper:], [:lower:], and [:digit:] chars
[:lower:] Lowercase alphabetic characters
[:print:] Printable characters
[:punct:] Punctuation characters
[:space:] Space characters (nonprinting),such as carriage return,newline,vertical tab, and form feed
[:upper:] Uppercase alphabetic characters
[:xdigit:] Hexidecimal characters

Quantifier Characters
* Match 0 or more times
? Match 0 or 1 time
+ Match 1 or more times
{m} Match exactly m times
{m,} Match at least m times
{m, n} Match at least m times but no more than n times
\n Cause the previous expression to be repeated n times

Alternative Matching And Grouping Characters
| Separates alternates, often used with grouping operator ()
( ) Groups subexpression into a unit for alternations,for quantifiers,or for back referencing (see "Backreferences" section)
[char] Indicates a character list; most metacharacters inside a character list are understood as literals, with the exception of character classes, and the ^ and - meta characters

SELECT * FROM scott.emp WHERE REGEXP_LIKE(ENAME,'^FR$') ;
or
SELECT * FROM scott.emp WHERE ENAME LIKE 'FR'


SELECT REGEXP_REPLACE('FYICenter.com', '*.com','i') FROM DUAL;
or
SELECT REGEXP_REPLACE ('FYICenter.com', '^*.com$','i') FROM DUAL;



Here we search for strings that have either an "a", "c", or "f" as the second character of the string.
select REGEXP_REPLACE( 'paddy', '^.[acf]' ) from dual

SELECT REGEXP_REPLACE ('FYICenter.com', '^f.*$','i') FROM DUAL;

SELECT REGEXP_REPLACE ('FYICenter.com', '^Y.*$','i') FROM DUAL;

SELECT REGEXP_REPLACE ('FYICenter.com', '*Y.*$','i') FROM DUAL;


SELECT cust_email old_email,REGEXP_REPLACE(cust_email,'@.*\.COM','@BIRDS.COM') new_email FROM oe.customers;


SELECT REGEXP_REPLACE('H1234 H4321 H2345 H2345','(.*) (.*) (.*) (.*)','\4 ,\3, \2, \1')FROM dual;