469,326 Members | 1,463 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

10g REGULAR EXPRESSIONS (REGEXP_LIKE)

amitpatel66
2,367 Expert 2GB
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT zip
  3.   FROM zipcode
  4.  WHERE REGEXP_LIKE(zip, '[^[:digit:]]') 
  5.  
  6. ZIP
  7. -----
  8. ab123
  9. 123xy
  10. 007ab
  11. abcxy 
  12.  
In case if tha pattern is used as '^[[:digit:]]' then it would look for zip code starting with digits.
Oct 9 '07 #1
1 13415
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
Nov 23 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Sehboo | last post: by
4 posts views Thread by Egyd Csaba | last post: by
7 posts views Thread by Billa | last post: by
3 posts views Thread by a | last post: by
25 posts views Thread by Mike | last post: by
13 posts views Thread by Wiseman | last post: by
12 posts views Thread by FAQEditor | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.