If I have a column called 'Categories' with say
Disclaimer: The commentators are right... CSVs in a single field are a horrible design, and should be re-done.
With that said, here's how you can work around your problem:
Categories with leading and trailing
,, that way you can include them in your wildcard search:
WHERE (',' + Categories + ',') LIKE '%,science,%'