help with pattern match REGEXP etc [message #640846] |
Wed, 05 August 2015 18:44 |
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 |
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 |
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.
|
|
|
|
|