oracle10g regular expression how to

[cc82394]

I need to compare values from one column (pvalue) to another column (pstnrd) that holds expected standard values that are complex. I want to use regular expressions. For example the standard is any variation of 4,5,6. A pvalue of 4,4,6 is true and a pvalue 6,8,4 is false when compared to the pstndrd.

Suggestions?

Thanks,
Patrick

 

[jimirvine]

Hmm, sorry Patrick, your matching 'rule' is pretty ambiguous.
Does 446 match 456 because it is a subset, or because it starts with the same number, or because it starts with a matching number and ends with a matching number or does it match because it starts with a matching number, ends with a matching number and only contains numbers that are a subset of the standard. I would suggest that you give us exactly what you are actually trying to do rather than trying to simplify it. Supply the ddl and dml to build a test case of reasonable proprtions along with expected results and whatever you have already tried.
Cheers
Jim

PS is there a particular reason that you need to use regular expressions or is it just because you 'think' it should work?

 

[cc82394]

I'm interesting in trying to figure out how to do this using regular expressions as I am trying to get more familiar with regular expressions in Oracle.

The standard value is listed as 4,5,6
The actual value received is 4,4,6
The match is because there are 3 numbers X,X,X AND that those three numbers are either 4, 5 or 6.

I am thinking that I need to do the reg_instr and first look for the number of commas. Then to a substep search for verifying that all numbers are either 4 or 5 or 6.

I'll dig up the dml/ddl and send it over as well.

Thanks,
Patrick

 

[cc82394]

create table nps(nps_key number, pname varchar2(10), svalue varchar2(10));

insert into nps values(1,'Paramx','4,5,6');

create table np_data(nps_key number, tstamp date, pvalue varchar2(10));

insert into np_data values(1,sysdate,'4,4,6'); --good match
insert into np_data values(2,sysdate,'4,4,4'); --good match
insert into np_data values(3,sysdate,'4,6,5,5'); --bad match due to 4 characters instead of 3
insert into np_data values(4,sysdate,'6,4,4'); --good match

--what I would do if the standard value (svalue) was a simple one to one match with a parameter value (pvalue)
select *
from nps a,
np_data b
where a.nps_key = b.nps_key
and b.pvalue = a.salue -- for example 4 = 4

But in this example the standard value is a string with three characters that can be any permutation of the numbers listed.

Suggestions?

Thanks,
Patrick

 

[jimirvine]

'Thanks for the test case. 'fraid the request is beyong my meagre skills with regexp at the mo (and I don't have time at the mo to play) Hopefully someone else will come up with something for you. If I think of anything, I'll post back. Although, I must admit, I think I'd be more inclined to usethis a collection and MEMBER OF.

 

[cc82394]

Ok I think I figured it out but I made a mistake in my inserts above that need to be corrected. If you ran the dml and ddl from above, then also run this:

update np_data
set nps_key = 1
where nps_key <> 1;
commit;

select *
from nps a,
np_data b
where a.nps_key = b.nps_key
and regexp_like(b.pvalue,'^[456],[456],[456]$');

--the above line is what I needed except it doesn't use a.svalue. But at least it gets me figuring out how to use regular expressions


Thanks,
Patrick