10g REGULAR EXPRESSIONS (REGEXP_LIKE)

[amitpatel66]

Let us have a look at the examples for each of the above mentioned SQL functions and WHERE clause operator

REGEXP_LIKE

Syntax:

REGEXP_LIKE(source_string, pattern [, match_parameter])

source_string - can be column of CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2, and NCLOB but not LONG datatypes

pattern - the regular expression

match_parameter - allows optional parameters such as handling the newline character, retaining multiline formatting, and providing control over case-sensitivity

Example:

The following SQL query searches the ZIP column for a pattern that satisfies the regular expression [^[:digit:]]. It will retrieve those rows in the ZIPCODE table for which the ZIP column values contain any character that is not a numeric digit.

 
SELECT zip
  FROM zipcode
 WHERE REGEXP_LIKE(zip, '[^[:digit:]]') 
 
ZIP
-----
ab123
123xy
007ab
abcxy 
 

In case if tha pattern is used as '^[[:digit:]]' then it would look for zip code starting with digits.

[Subash Lenka]

select * from reg_table where regexp_like (ename,'[A-Za-z]+[0-9]*$')
and not regexp_like (ename,'^[A-Za-z]*$')

To find only alphanumeric