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

    TheUrbanBrain

      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. 

       

         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.