Couple of ways I can think of:
1. Create a look up table - something like this:CREATE TABLE dbo.CodeLookup
(code CHAR(4))
INSERT INTO dbo.CodeLookup VALUES
('M123'),('N321')
SELECT
SUM(ProductNum)
FROM
Table1 t1
INNER JOIN dbo.CodeLookup c
ON t1.Model LIKE c.code + '%';
2. Use dynamic SQL. Even though usually one would try to avoid dynamic sql if at all possible, this is one of those cases you might consider it.I prefer the lookup table, but you may need dynamic sql if your join condition changes for each lookup value (as in your example, it is RIGHT(Model,4) in one case and LEFT(Model,4) in another case. There is a discussion of the reasons for doing this and the safe way to do this at Gail Shaw's blog here: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
how to branch the where clause?
Posted - 11/15/2012 : 13:44:37
