Showing posts with label REGEXP_REPLACE. Show all posts
Showing posts with label REGEXP_REPLACE. Show all posts

Oct 29, 2010

To get only numeric values

To  retrieve only numeric values from both numeric& character value

In 10 g

Select  regexp_replace(regexp_replace('5RTYDDB','([[:punct:]])'),'([[:alpha:]])')
  from dual t 
 
In 9i  
 
 select replace('BBd3',TRANSLATE('BBd3', ' +-.0123456789', ' '),'') from dual

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

Nov 24, 2009

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;

Sep 9, 2009

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}');

Jul 15, 2009

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;