Mike just came to me with an interesting problem. One of the tables that he needs to query has some comma separated values. He needs to search for a specific value in that column. There was no time or opportunity to refactor the table into a joining table, so it needed to be based around the Like function.

His sample data contained a column Keyword with data like the following:
Sport
Transport
Sport, Transport
Transport, Media, Sport

He was looking for the keyword "Sport" and was matching all of the above 4 rows, rather than just rows 1, 3 and 4.

It took me a bit of head scratching, but I finally proposed the following solution:
SELECT *
FROM Table
WHERE ' ' + REPLACE(Keyword,',',' ') + ' ' LIKE '% Sport %'
So, prefixing and suffixing the data with a space, and replacing the comma being used for separation with a space, and then comparing it against our target word which is also prefixed and suffixed by a space.

This solution feels like a bit of a cludge, but less arduous than implementing a Split function. Opinions always welcomed