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