Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Regular Expression
Oracle Regular Expression [message #405139] Tue, 26 May 2009 08:39 Go to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi All,
I need to select only those records which have either numeric value or : or both (numeric and :).
create table oss_test_vip ( x varchar2(10) )

select  x
from oss_test_vip
where not REGEXP_LIKE(x,'(^[[:digit:]*[:]*]*$)') 

Problem is that above query also discarding NULL value too.
Please help me out.

[Updated on: Tue, 26 May 2009 08:40] by Moderator

Report message to a moderator

Re: Oracle Regular Expression [message #405141 is a reply to message #405139] Tue, 26 May 2009 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
add "or col is null"

Regards
Michel
Re: Oracle Regular Expression [message #405177 is a reply to message #405139] Tue, 26 May 2009 13:58 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Be careful with your regex, it will also select rows with digits, colons AND/OR astericks '*' and brackets '[]'.

You could rewrite it as:

select  x
from oss_test_vip
where REGEXP_LIKE(x,'^[[:digit:]:]*$') ;

Also didn't know why you used WHERE NOT when you actually wanted those rows ?

[Updated on: Tue, 26 May 2009 14:16]

Report message to a moderator

Previous Topic: Max File length with utl_file
Next Topic: Getting all Linked rows
Goto Forum:
  


Current Time: Fri Dec 02 23:23:21 CST 2016

Total time taken to generate the page: 0.05929 seconds