We all are familiar with the classic "LIKE" operator, and we all love it.
Like this:

Select * from Customers where Name like '%andy%'

What if we want to do something different?
Say, we have a list of IDs, and a table we'd like to filter by these IDs.

One way is to use "IN" operator:
SELECT * FROM Customers
WHERE ID IN (1,2,43,23)

But what if we get the list of IDs in a single varchar parameter?
Here we go!

DECLARE @ID varchar(300)

SET @ID='1,2,43,23' -- Define the IDs list
SET @ID=',' + @ID + ',' -- Prepend/append the commas

SELECT * FROM Customers
WHERE @ID LIKE '%,' + convert(varchar(20),ID) + ',%'

Voila!


And the little bit advanced join :)

create table #filter
(
Category varchar(300),
FilterString varchar(300)
)

INSERT INTO #filter SELECT 'Error', 'fail'
INSERT INTO #filter SELECT 'Error', 'error'
INSERT INTO #filter SELECT 'Error', 'cancelled'

INSERT INTO #filter SELECT 'OK', 'complete'
INSERT INTO #filter SELECT 'OK', 'accomplished'


Then we have a table named #log:
create table #log
(
TimeStamp varchar(300),
Message varchar(300)
)

INSERT INTO #log SELECT '1/1/2011', 'Operation failed beee bee bee'
INSERT INTO #log SELECT '1/1/2011', 'Error occured'
INSERT INTO #log SELECT '1/1/2011', 'Task completed'
INSERT INTO #log SELECT '1/1/2011', 'Operation failed beee bee bee'
INSERT INTO #log SELECT '1/1/2011', 'Operation failed beee bee bee'


And then comes the JOIN!
SELECT * FROM #log
JOIN #filter ON
#log.Message LIKE '%' + #filter.FilterString + '%'