How do I write a select statement to see if table field is in variable list

I was wondering if someone knew the best way to write a query to pull back rows where a table field is found in a a variable string.  I'm using Cold Fusion 10 on Microsoft SQL Server.

The table has a series of fields such as

carcode          carname

G                Garage

C                Carport

A               Attached

D               Detached

And another query pulls back a field that stores as text all the abbreviations that apply

I want to pull back all the carnames that would match the abbreviations stored in that field.  I thought I could use a contains line but that does not work.  How do I pull back matches against a variable string... I tried an IN statment but that didn't work.

CFQUERY...

Select carname from CarTable WHERE CONTAINS (carcode, '#query.carlist#') does not work. 

I'm stumped.  The list of abbreviations could be very LOOOONG or short and I just need to translate those abbreviations into the full names by grabbing it from another table.  It isn't just one abbreviation so I can't use LIKE in the one to one sense.

Thank you so much.