Home » SQL & PL/SQL » SQL & PL/SQL » Regular Expression (3 threads merged by bb)
Regular Expression (3 threads merged by bb) [message #385347] Mon, 09 February 2009 04:02 Go to next message
convey05
Messages: 43
Registered: December 2007
Location: CHENNAI
Member
Hi,
I need to extract data other than 4alphabets followed by number.
Eg:
I have data like
ABCD08322
File uploaded
File uploaded -1
XYZC93223
Cancelled
HHHH08/90
In this case I need only File uploaded,File uploaded -1 and cancelled data.
How to get this record using Regular Expression.

Thanks in advance
Re: Regular Expression [message #385348 is a reply to message #385347] Mon, 09 February 2009 04:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As always: Post a Test case: create table and insert statements along with the result you want with these data.
Why "HHHH08/90" is not in your result set, it is not a row with "4alphabets followed by number".
Also post what you already tried.

And don't spam my mailbox with a copy of your topic.

Regards
Michel

[Updated on: Mon, 09 February 2009 04:10]

Report message to a moderator

Re: Regular Expression (3 threads merged by bb) [message #385354 is a reply to message #385347] Mon, 09 February 2009 04:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
with src as (select 'ABCD08322' col_1 from dual union all
             select 'File uploaded' col_1 from dual union all
             select 'File uploaded -1' col_1 from dual union all
             select 'XYZC93223' col_1 from dual union all
             select 'Cancelled' col_1 from dual union all
             select 'HHHH08/90' col_1 from dual)
select col_1 
from   src
where  not regexp_like(col_1,'[A-Z]{4}[0-9]');
Re: Regular Expression (3 threads merged by bb) [message #385359 is a reply to message #385354] Mon, 09 February 2009 04:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Depends on the meaning of "followed by number".

Regards
Michel
Re: Regular Expression (3 threads merged by bb) [message #385362 is a reply to message #385359] Mon, 09 February 2009 05:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Absolutely true.
Hopefully it'll give the OP something to think about other than posting his question again and again.
Re: Regular Expression (3 threads merged by bb) [message #385379 is a reply to message #385354] Mon, 09 February 2009 06:02 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Quote:
where not regexp_like(col_1,'[A-Z]{4}[0-9]')
You're on to something in your expression, but check out this page, especially the part about anchoring metacharacters.
Regular expressions in Oracle database

Your expression would also match the string HHHHH08/90, 7HHHH08/90 or FILEUPLOADED1, so they wouldn't be included in the query results. (And I think you wanted expressions like those also to be shown in your results convey05?) How about abcd08322? Should it give a hit or not? A-Z only matches upper case characters. [:alpha:] gives you both upper and lower case characters. Look at the page I gave you. The expressions needed are there.
Re: Regular Expression (3 threads merged by bb) [message #385409 is a reply to message #385379] Mon, 09 February 2009 07:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you read the OP with a sufficiently mischevious attitude, you'll see that he doesn't ever ask for data that starts with 4 characters and a number.

All his examples happen to be that way, but that's his problem for not providing enough test data.

I maintain that my answer is exactly what he asked for, although I'll readily concede that it's probably not what he wants.

Actually, you make a good point about the case.
I'll accept that my answer would have been better as
where  not regexp_like(col_1,'[A-Za-z]{4}[0-9]');


[Added case details]

[Updated on: Mon, 09 February 2009 07:48]

Report message to a moderator

Re: Regular Expression (3 threads merged by bb) [message #385413 is a reply to message #385379] Mon, 09 February 2009 08:25 Go to previous messageGo to next message
convey05
Messages: 43
Registered: December 2007
Location: CHENNAI
Member
Hi,
I have data with space also like

Create table test(COL1 VARCHA2(100));
Insert into test values('ABCD090890');
Insert into test values('File upload');
Insert into test values('XYZH90899');
Insert into test values('ABCD 902332');
Insert into test values('File Rec\progress');

Commit;

From this i should get only File upload and File Rec\progress data only.

Kindly tell me how to do.


Thanks in advance
Re: Regular Expression (3 threads merged by bb) [message #385415 is a reply to message #385413] Mon, 09 February 2009 08:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Kindly answer the previous questions, FIRST.

Regards
Michel
Re: Regular Expression (3 threads merged by bb) [message #385416 is a reply to message #385413] Mon, 09 February 2009 08:42 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Using JRowbottom's expression (you had a good point about the question asked not being completely precise):

To be able to find entries with any number of spaces (zero, one, or several) between the letters and the number:
[a-zA-Z]{4} *[0-9]

Alternatively if you want the expression to start with four characters followed by a number (rather than just contain four characters followed by a number)
^[a-zA-Z]{4} *[0-9]
Re: Regular Expression (3 threads merged by bb) [message #385827 is a reply to message #385347] Wed, 11 February 2009 04:39 Go to previous messageGo to next message
convey05
Messages: 43
Registered: December 2007
Location: CHENNAI
Member
Hi,
I need data like File cancelled,File Up,Booking/Fully Received.
I gave qry like this.It is fetching the data.
I like to know whether we have any other way to get the data

with scr1 as (
SELECT 'ABCD0923232' SCR FROM DUAL UNION ALL
SELECT '132ABC3232' SCR FROM DUAL UNION ALL
SELECT 'A32BC3232' SCR FROM DUAL UNION ALL
select 'File Up' scr from dual union all
select '/s' scr from dual union all
select 'xyza9209-2' scr FROM DUAL union all
select 'File cancelled' scr from dual union all
select 'Booking/Fully Received' scr from dual union all
select '2X40 HQ' scr from dual
)
SELECT scr
FROM scr1
where regexp_like(scr,'[:digit:]')

Thanks in advance
Re: Regular Expression (3 threads merged by bb) [message #385829 is a reply to message #385827] Wed, 11 February 2009 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You still don't answer the questions we posted, you still don't specify your requirements, how could we answer your question?

Quote:
I like to know whether we have any other way to get the data

Which ones?

Regards
Michel
Re: Regular Expression (3 threads merged by bb) [message #385834 is a reply to message #385827] Wed, 11 February 2009 05:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can do it without regular expressions like this:
with scr1 as (
 SELECT 'ABCD0923232' SCR FROM DUAL UNION ALL
 SELECT '132ABC3232' SCR FROM DUAL UNION ALL
 SELECT 'A32BC3232' SCR FROM DUAL UNION ALL
 select 'File Up' scr from dual union all
 select '/s' scr from dual union all
 select 'xyza9209-2' scr FROM DUAL union all
 select 'File cancelled' scr from dual union all
 select 'Booking/Fully Received' scr from dual union all
 select '2X40 HQ' scr from dual 
 )
 SELECT scr
 FROM scr1
 where translate(scr,'1234567890','          ') = scr;


Given how badly wrong you've got your regular expression, you might want to read up on them.
Your Regexp_Like is looking for any of the characters ':','d','i','g','i', or 't'

You probably meant the expression to be
where not regexp_like(scr,'[ [:digit:]]');


Fix really annoying WIKI conversion for regular expressions

[Updated on: Wed, 11 February 2009 05:36]

Report message to a moderator

Re: Regular Expression (3 threads merged by bb) [message #385835 is a reply to message #385829] Wed, 11 February 2009 05:34 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
Hope You are aware, that there is a large difference between
  ...where regexp_like(scr,'[:digit:]')

and 

  ...where regexp_like(scr,'[ [:digit:]]')

The first expression finds scr that match any single character in the list within the brackets, so any of ':dgit'

The second matches a digit character (POSIX).

Oops, JRowbottom was faster Embarassed

[Updated on: Wed, 11 February 2009 05:36]

Report message to a moderator

Re: Regular Expression (3 threads merged by bb) [message #386224 is a reply to message #385347] Fri, 13 February 2009 03:25 Go to previous message
convey05
Messages: 43
Registered: December 2007
Location: CHENNAI
Member
Hi,
Thank you
Previous Topic: Want to se Arithmetic Operators Dynamically
Next Topic: Web Page and PL/SQL
Goto Forum:
  


Current Time: Wed Dec 07 14:49:32 CST 2016

Total time taken to generate the page: 0.14374 seconds