SQL: How to check a string for multiple values from another table

Question

I am trying to parse through a string to see if values from another table appear in it in any position.

select ROUTE,
case when ROUTE like '%' || b.AIRPORTCODE || '%' then 1 
else 0 end as CLASS_B,
case when ROUTE like '%' || c.AIRPORTCODE || '%' then 1 
else 0 end as CLASS_C
from FLIGHT_MESSAGE, 
(select * from CLASS_B_C_AIRPORTS where CLASS_B_C = 'B') b,
(select * from CLASS_B_C_AIRPORTS where CLASS_B_C = 'C') c

The CLASS_B_C_AIRPORTS table will have an airport code (KDCA) and whether or not it is 'B' or 'C'.

In my example below, ROUTE field will contain a string of text like:

KDCA..FLUKY.DCA246.PAUKI..MOL.FLCON6.KRIC/0127

For this string, I'd like to return the following, because KDCA is a Class B airport and KRIC is a Class C airport:

| ROUTE                                          | CLASS_B | CLASS_C |
----------------------------------------------------------------------
| KDCA..FLUKY.DCA246.PAUKI..MOL.FLCON6.KRIC/0127 | 1       | 1       |

This query currently returns 0's for Class B and Class C against this string.

So, what does "1" represent in your results? 1 = true, 0 = false? (there is at least one CLASS_B airport code in your string and at least one CLASS_C airport)? Or are they counts of CLASS_B and CLASS_C airport codes in the input string? — user5683823
– user5683823, Commented Feb 16, 2017 at 23:57
Also, how do you plan to avoid the "mother is in chemotherapy" problem? What if your input string has the substring MVFA, and there is a CLASS_B airport with code VFA, or MVF? — user5683823
– user5683823, Commented Feb 17, 2017 at 0:01

Gordon Linoff · Accepted Answer · 2017-02-16 22:21:39Z

1

I think this is what you want:

SELECT ROUTE,
       MAX(CASE WHEN CLASS_B_C = 'B' THEN 1 ELSE 0 END) as CLASS_B,
       MAX(CASE WHEN CLASS_B_C = 'C' THEN 1 ELSE 0 END) as CLASS_C
FROM FLIGHT_MESSAGE fm JOIN 
     CLASS_B_C_AIRPORTS a
     ON fm.ROUTE LIKE '%' || a.AIRPORTCODE || '%'
GROUP BY ROUTE;

answered Feb 16, 2017 at 22:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Quang Nguyen Over a year ago

That is returning a 0 for Class B and 1 for Class C. It should be 1 and 1.

Gordon Linoff Over a year ago

@QuangNguyen . . . I suspect something is wrong with your data. I think this query should do what you intend (although you might want a left join).

user5683823 Over a year ago

This will almost surely require some refinement (working with the OP) to avoid the "mother is in chemotherapy" problem. (You are searching for the code "mother" and you find it, inadvertently, in the input string "chemotherapy".)

Collectives™ on Stack Overflow

SQL: How to check a string for multiple values from another table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related