Alternatives to Like/Like Any

Database

Alternatives to Like/Like Any

<p>Hello all,</p>

<p>&nbsp;</p>

<p>I have a dataset that resembles the below:</p>

<p>Customer_ID | Targeting_Region</p>

<p>12 | targ=EU, targ=!Eu.Fr</p>

<p>34 | targ=Asia</p>

<p>56 | targ=!EU</p>

<p>&nbsp;</p>

<p>The &#39;!&#39; denotes &#39;does not equal&#39;. For example, the customer in Row #1 wants to target the EU, but exclude France.&nbsp;</p>

<p>&nbsp;</p>

<p>I want to create a field that flags (with a &#39;1&#39;) any row where there is &#39;positive&#39; targeting. By &#39;positive&#39; targeting I am referring to any row where a specific region as been explicitly INCLUDED (&#39;negative&#39; targeting would be where a region is explicity EXCLUDED, such as the exclusion of France in Row #1). For example, Row #1 contains both positive and negative targeting, Row #2 contains only positive targeting, Row #3 contains only negative targeting.</p>

<p>The problem I am encountering is that a simple case statement won&#39;t work (as far as I can tell). I have tried the 2 statements below:</p>

<pre class="brush:sql;">

(case when targeting_region like &#39;%targ=%&#39; then 1 else 0 end) as target_flag</pre>

<pre class="brush:sql;">

(case when ((targeting_region like &#39;%targ=%&#39;) and (targeting_region not like &#39;%targ=!%&#39;)) then 1 else 0 end) as target_flag 

</pre>

<p>The 1st statement above doesn&#39;t work because it will return 1 for both &#39;targ=&#39; and &#39;targ=!&#39;</p>

<p>The 2nd statement above doesn&#39;t work because it will return 1 for rows that <u>only</u> have positive targeting. As such, Row #1 (above) would return a 0 &nbsp;(I want it to return a 1).</p>

<p>&nbsp;</p>

<p>Any ideas on how I could accomplish this?</p>

<p>&nbsp;</p>

<p>Thanks!</p>

3 REPLIES

Re: Alternatives to Like/Like Any

Apologies, not sure what happened with the formatting above. See below for a readable version. :)

Hello all,

I have a dataset that resembles the below:

Customer_ID | Targeting_Region

12 | targ=EU, targ=!Eu.Fr

34 | targ=Asia

56 | targ=!EU

The '!'; denotes 'does not equal'. For example, the customer in Row #1 wants to target the EU, but exclude France.

I want to create a field that flags (with a '1') any row where there is 'positive' targeting. By 'positive' targeting I am referring to any row where a specific region as been explicitly INCLUDED ('negative' targeting would be where a region is explicity EXCLUDED, such as the exclusion of France in Row #1). For example, Row #1 contains both positive and negative targeting, Row #2 contains only positive targeting, Row #3 contains only negative targeting.

The problem I am encountering is that a simple case statement won't work (as far as I can tell). I have tried the 2 statements below:

(case when targeting_region like '%targ=%' then 1 else 0 end) as target_flag


(case when ((targeting_region like '%targ=%') and (targeting_region not like '%targ=!')) then 1 else 0 end) as target_flag 

The 1st statement above doesn't work because it will return 1 for both 'targ=' and 'targ=!'

The 2nd statement above doesn't work because it will return 1 for rows that only have positive targeting. As such, Row #1 (above) would return a 0 (I want it to return a 1).

Any ideas on how I could accomplish this?

Thanks!

Enthusiast

Re: Alternatives to Like/Like Any

(case when ((targeting_region like '%targ=%') and (targeting_region  like '%targ=!%')) then 1 else 0 end) as target_flag

Do you want only the 1st row to be displayed when you select and not the 2nd and 3rd. The above should work in that case. The reason you get a 0 is because of the lack of % after '%targ=!' and also that you never had the 2 conditions in the data of the 1st row. if you want the 2nd and 3rd rows to be displayed along with the 1st then you do an OR with targeting_region like '%targ=%'.

The first statement that you have written does not work due to the data you have that all the 3 rows have '%targ=%' in them. Hence all the 3 would retrun a value of 1 in the case.

Re: Alternatives to Like/Like Any

Regular expression should work for you.

CASE WHEN REGEXP_SIMILAR ('targ=EU, targ=!Eu.Fr', '!', 'c') = 1 THEN 0
ELSE 1
END AS target

Regards,

Nishchint