I have a list and want to know if my column contains any of the lists values.
Hello, I have a table with column colors which has one or many colors as inputs:
Example:
Column : Colors
Row 1 : RED
Row 2 : RED and GREEN
Row 3 : RED and ORANGE
Row 4 : ORANGE
Row 5 : GREEN AND MAGENTA
Row 6 : ORANGE AND BLACK
…..
My colleague gave me a list of 25 unique colors and wants to know if any of these colors are in my table.
I did this:
select
colors
, case when colors like '%GREEN%' then 1
when colors like '%RED%' then 1
when colors like '%PURPLE%' then 1
...
else 0
end found_colors
from my_table
but you can see that this is not efficient since I had to manually type each like and the colors.
I tries exist but but it has 0 as output for “RED and GREEN”.
I believe there is a better way to do it.
Any suggestions?
BTW I am using pl/sql
Regards,
Nick
Nika Kalichava is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1