Select for a substring in array in DB2

Question

I have the following Car's table in DB2:

 id     reference
 1      31943149-blue
 2      40213982-red
 3      93713946-blue
 ...

Then I need to find all entries which reference ending is inside a dynamic list of colors. How could I do that? In fact, the dynamic list of colors should be found from another query to another table, so I guess than an UNION or INNER JOIN could be better in terms of performance.

Thanks in advance

WHERE reference LIKE '%blue' OR reference LIKE '%red' OR .... . Prepare the query dynamically using the application language code (eg: PHP) — Madhur Bhaiya
– Madhur Bhaiya, Commented Sep 3, 2019 at 5:59
But the array of colors is dynamic, I don't know exactly which ones will be — FVod
– FVod, Commented Sep 3, 2019 at 6:00
That is why I said prepare the query string dynamically. Eg: use foreach .. loop — Madhur Bhaiya
– Madhur Bhaiya, Commented Sep 3, 2019 at 6:01
But I could have a very large list of colors, how would be the performance with lots of LIKE OR ? — FVod
– FVod, Commented Sep 3, 2019 at 6:01
You can use REGEXP_LIKE with the regexp red|blue|green. You can construct this regexp dynamically from the list of colors. — Barmar
– Barmar, Commented Sep 3, 2019 at 6:07

Barmar · Accepted Answer · 2019-09-03 06:10:46Z

2

You can join the two tables, using LIKE in the ON condition.

SELECT car.*
FROM car
JOIN colors ON car.reference LIKE ('%-' CONCAT colors.color)

answered Sep 3, 2019 at 6:10

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

Collectives™ on Stack Overflow

Select for a substring in array in DB2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related