performing a sort of "reverse lookup" in sql server

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,开发者_StackOverflow 52498762 vs. 52498762-A4). Depending on the suffix used (there are two or three), any 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:

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'

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?

Why not get both sets of comments at once?

SELECT
   ...
FROM
   Products P
   LEFT JOIN Comments C
      ON P.ProductID LIKE C.SpecID + '%'
      OR P.ProductID LIKE '%-' + C.SpecID

Also you could consider:

SELECT
   ...
FROM
   Products P
   LEFT JOIN Comments C
      ON (Len(C.SpecID) = 2 AND P.ProductID LIKE C.SpecID + '%')
      OR (Len(C.SpecID) > 2 AND P.ProductID LIKE '%-' + C.SpecID)

Testing is in order to see if one performs better than the other. If you find the queries to be too slow, then trying adding some persisted calculated columns: in Products to specify whether the product ID has a dash in it or not, and in Comments add two columns, one with only product IDs and one with only suffices. Indexes on these columns could help.

ALTER TABLE Comments ADD ExactSpecID AS 
   (CASE WHEN Len(SpecID) > 2 THEN SpecID ELSE NULL END) PERSISTED
ALTER TABLE Comments ADD Suffix AS 
   (CASE WHEN Len(SpecID) = 2 THEN SpecID ELSE NULL END) PERSISTED

I don't really know your layout but...

SELECT
    p.ProductID,
    p.ProductName,
    c.CommentText
FROM
    Products p
    INNER JOIN Comments c
        ON 
           RIGHT(p.ProductID, LEN(p.ProductID) - CHARINDEX('-', p.ProductID))
           = 
           c.SpecID

So that should give you all the characters before the '-' in your ProductID string. And match that to the SpecID on the comments table? Is that what you were looking for?