Proc sql in Oracle: Using Regular Expressions instead of LIKE Function criteria

RobertNYC — Thu, 09 Feb 2017 17:53:56 GMT

Hi all,

Below is a proc sql Oracle query I’ve written which pulls various promotion history based on elements in a string var. I would like very much to use regular expressions as opposed to a bunch of LIKE functions. I’m having trouble figuring out how to do this in the Oracle environment. The section of the query I would like to modify using regular expressions is this:

and t1.segment_key not like '%N'

and t1.segment_key not like '%C'

and t1.segment_key not like 'CNM'

and (t2.client_promo_id like 'PD%'

or t2.client_promo_id like 'FD%'

or t2.client_promo_id like 'RD%'

or t2.client_promo_id like 'PE%'

or t2.client_promo_id like 'FE%'

or t2.client_promo_id like 'RE%'

or t2.client_promo_id like 'RLS%'

or t2.promotion_title like '%RL%')

and t2.promotion_title not like '%RGB%'

and t2.promotion_title not like '%Rugby%'

Any assistance will be greatly appreciated. Thanks!

Full query:

proc sql;
connect to oracle (user=xxx path='xxx');
create table Want as
select  * from connection to oracle (
select    
t1.customer_id, 
count(distinct case when t1.promotion_type=1 then t1.promotion_id end) as dm_ttl,
count(distinct case when t1.promotion_type=2 then t1.promotion_id end) as email_ttl
from sas_promo_history_vw t1 join sas_promotion_vw t2       
 on  t1.promotion_id=t2.promotion_id
where t1.promotion_type in (1,2)
and t2.in_home_date between &start_date. and &End_date.
and t1.segment_key not like '%N' 
and t1.segment_key not like '%C' 
and t1.segment_key not like 'CNM' 
and (t2.client_promo_id like 'PD%' 
or t2.client_promo_id like 'FD%' 
or t2.client_promo_id like 'RD%'  
or t2.client_promo_id like 'PE%' 
or t2.client_promo_id like 'FE%' 
or t2.client_promo_id like 'RE%'
or t2.client_promo_id like 'RLS%'
or t2.promotion_title like '%RL%')
and t2.promotion_title not like '%RGB%' 
and t2.promotion_title not like '%Rugby%'
group by t1.customer_id);
disconnect from oracle;quit;

Re: Proc sql in Oracle: Using Regular Expressions instead of LIKE Function criteria

FriedEgg — Thu, 09 Feb 2017 18:04:53 GMT

...
and not regexp_like (t1.segment_key, '[NC]$')
and t1.segment_key != 'CNM'
and (
  regexp_like (t2.client_promo_id, '^([FR]D|[PFR]E|RLS)')
  or t2.promotion_title like '%RL%' )
and not regexp_like (t2.promotion_title,'(RGB|Rugby)')
...

Re: Proc sql in Oracle: Using Regular Expressions instead of LIKE Function criteria

RobertNYC — Fri, 10 Feb 2017 13:49:59 GMT

The is beatiful! Thanks!!!!

topic Proc sql in Oracle: Using Regular Expressions instead of LIKE Function criteria in SAS Procedures

Proc sql in Oracle: Using Regular Expressions instead of LIKE Function criteria

Re: Proc sql in Oracle: Using Regular Expressions instead of LIKE Function criteria

Re: Proc sql in Oracle: Using Regular Expressions instead of LIKE Function criteria