Home » SQL & PL/SQL » SQL & PL/SQL » help with pattern match REGEXP etc (Oracle 11, 12)
icon3.gif  help with pattern match REGEXP etc [message #640846] Wed, 05 August 2015 18:44 Go to next message
namitanamburi
Messages: 35
Registered: March 2009
Member
Hello,

Here is the code


--Query 1

SELECT CASE WHEN 
REGEXP_LIKE( '5 SEC IN 20.0 MIN', '^[0-9]*[0-9] SEC IN [0-9]*[0-9]\.[0-9]*[0-9] MIN$') THEN 'TRUE' 
ELSE 'NULL'
END
FROM dual;

--Query 2

SELECT CASE WHEN 
REGEXP_LIKE( '0.55 SEC IN 200.0 MIN', '^[0-9]*[0-9]|\.[0-9]*[0-9] SEC IN [0-9]*[0-9]\.[0-9]*[0-9] MIN$') THEN 'TRUE' 
ELSE 'NULL'
END
FROM dual;






In the query 1 the number before SEC can include a decimal , so it scan be a 5 or .5 or 777.5 or 777777.5555

So I improvised and built query 2 so that I also retrieve patterns having number with orwithout decimal, I included pipe | which is OR condition, which is giving the 5 or .5 but also returning a string like below which is not what i want.

0.5 200 SEC IN 20.0 MIN
0.5 200 NO-SEC IN 20.0 MIN


I'm interested in XXXXXX SEC IN XXXXXX MIN only.


where XXXXX = number with or without decimal


Please advise.


Re: help with pattern match REGEXP etc [message #640848 is a reply to message #640846] Thu, 06 August 2015 00:08 Go to previous messageGo to next message
bugfox
Messages: 18
Registered: October 2010
Junior Member
with tst as (select '5 SEC IN 20.0 MIN' str from dual
             union all
             select '0.55 SEC IN 200.0 MIN' from dual
             union all
             select '0.5 200 NO-SEC IN 20.0 MIN' from dual)
select str,
       case
          when regexp_like(str, '^\d+\.?\d* SEC IN \d+\.?\d* MIN$') then
             'true'
       else
          'false'
       end   
from tst
Re: help with pattern match REGEXP etc [message #640915 is a reply to message #640846] Thu, 06 August 2015 12:12 Go to previous messageGo to next message
namitanamburi
Messages: 35
Registered: March 2009
Member
Thank you Bugfox. Really appreciate that.

Instead of just taking your answer, I would like to understand it.


^ - start of pattern

\d - number of any length

+  - plus additional pattern

\.?\d - now includes decimals

* - The zero or more operator '*', matches zero or more occurrences of the preceding character or subexpression



What is the ? for.............

How are we avoiding 200 in '0.5 200 NO-SEC IN 20.0 MIN'?

Please advise.
Re: help with pattern match REGEXP etc [message #640918 is a reply to message #640915] Thu, 06 August 2015 12:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
What is the ? for.............


In this case, the preceding pattern (character) may or may not exist.


Re: help with pattern match REGEXP etc [message #640931 is a reply to message #640918] Thu, 06 August 2015 19:09 Go to previous message
namitanamburi
Messages: 35
Registered: March 2009
Member
Thank You Michael, highly appreciate it.
Previous Topic: Update multiple columns in Case
Next Topic: Filtering data by aggregated columns calculated in a subquery.
Goto Forum:
  


Current Time: Sat Apr 20 00:58:08 CDT 2024