title: performing a sort of "reverse lookup" in sql server
body: <p>I have a table of spec numbers that require additional comments to be entered on a printed form when the form is generated. When I am generating that form, I have a list of products and their associated spec numbers. Some of the "normal" products are just plain old eight-digit numbers and I can easily search my comment table for a matching spec number and pull the appropriate comment. But there are also "special" products that have a suffix appended (for instance, 52498762 vs. 52498762-A4). Depending on the suffix used (there are two or three), <em>any</em> product with that suffix needs the comment listed. But when the comment table was set up, rather than specify each individual spec number with that suffix, the spec field has just the suffix. For example:</p>
  
  <pre><code>SpecID    |  Comment
  ------------------------------------------------------
  52498762  |  Comment for a specific spec number
  52746627  |  Comment for a different spec number
  A4        |  Comment for any spec that ends with '-A4'
  </code></pre>
  
  <p>So in this example, if I had a spec number of 52196748-A4, it would need the comment listed in the 'A4' row of the comment table. I could have two hundred specs ending in -A4, and they would all get the same comment. Is there a way of writing a query that matches a wildcard version of the table contents? I know normally one matches a fixed value in a table against a wildcard user-supplied value; what I need is a static user-supplied value and a wildcard table value. Is that even possible?</p>