How to write case condition to check particular value exist among multiple values in sql?

I have a column in table which is having single/multiple value. I have to convert it yes/no based on condition.

example:

rendition_type_sys
distribution
uploaded, distribution
uploaded
single

I need to change the value based on condition. If column having distribution then value should convert as ‘Yes’ otherwise ‘No’

Final Output:

rendition_type_sys
Yes
Yes
No
No

I tried one case statement but that is working for single value not multiple value-

case when ren.rendition_type__sys='distribution' then 'Yes' else 'No' end as rendition_type__sys

Answer

First, you should fix your data model so you are not storing multiple values in a string.

In the meantime, like should do what you want. For your example:

(case when ren.rendition_type__sys like '%distribution%'
      then 'Yes' else 'No'
 end) as rendition_type__sys

Note: In case “distribution” is part of an element name and you don’t want that, you can check for delimiters. In Standard SQL, this would be:

(case when ', ' || ren.rendition_type__sys || ', ' like '%, distribution, %'
      then 'Yes' else 'No'
 end) as rendition_type__sys

The string concatenation operator may vary depending on the database you are using.