with t as (
SELECT
'Test data format' as comment_txt from dual
union all
select
'13/01/19;9:50;1.0; 30.5; 5a10b22c' from dual
union all
select
'2016/04/13;12:00am;8.0;-0.5b;25.0; 2a5b22c' from dual
union all
select
'2016/04/15;12:00;8.0;-0.5;25.0; 12a23b22c' from dual) ---matched one
select * from t;
Hi,
I have free-text semicolon separated string in a comment column like above in my database. I am going to write a query return ‘match’ and ‘unmatch’ message for the comment column in each line. I know I can use regexp_match or regexp_like function to implement that but am struggling in how to write the regexp pattern.
Only the fourth line should be returned with ‘match’ message because it meets:
1 before first semicolon yyyy/mm/dd mm’s value is 01-12 dd’s vaue 01-31
2 between first and second semicolon hh:mm hh’s value 00-23 mm’s value 01-59
3 between second and third semicolon , a positive or negative decimal value
4 between third and fourth semicolon , a positive or negative decimal value
5 between fourth and fifth semicolon, a positive or negative decimal value
6 after fifth semicolon, XXaYYbZZc XX’s value any integer YY’s value 00-24 ZZ’s value 00-59
For example, in query,
select
CASE WHEN REGEXP_LIKE(T. comment_txt, '……' ) THEN
'MATCHES'
ELSE
'DOES NOT MATCH PATTERN'
END as match_msg
From t
Thanks in advance!!
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production