4 Replies Latest reply on Nov 7, 2012 10:54 AM by TheUrbanBrain

    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.




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


         select carname from CarTable WHERE CONTAINS(carcode, 'G,A')



      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.