SQL - How do I declare a group of people's names to be used in my query?

Question

I have 5 queries that I will be running all at the same time to return 5 different groups of data - what they do all have in common is my statement below:

HAVING  (   LEFT(comm.LONG_COMM_LINE_1, CHARINDEX('-', comm.LONG_COMM_LINE_1) - 2) LIKE ('%BRIAN%MILLER%') OR 
        LEFT(comm.LONG_COMM_LINE_1, CHARINDEX('-', comm.LONG_COMM_LINE_1) - 2) LIKE ('%LATEASHA%CALVERT%') OR
        LEFT(comm.LONG_COMM_LINE_1, CHARINDEX('-', comm.LONG_COMM_LINE_1) - 2) LIKE ('%CHERITA%MARTIN%') OR
        LEFT(comm.LONG_COMM_LINE_1, CHARINDEX('-', comm.LONG_COMM_LINE_1) - 2) LIKE ('%CARMALITA%MCQUEEN%') OR
        LEFT(comm.LONG_COMM_LINE_1, CHARINDEX('-', comm.LONG_COMM_LINE_1) - 2) LIKE ('%CRAIG%NADRATOWSKI%') OR
        LEFT(comm.LONG_COMM_LINE_1, CHARINDEX('-', comm.LONG_COMM_LINE_1) - 2) LIKE ('%SCOTT%SMOLINSKI%') OR
        LEFT(comm.LONG_COMM_LINE_1, CHARINDEX('-', comm.LONG_COMM_LINE_1) - 2) LIKE ('%ALISHA%WILLIAMS%') OR
        LEFT(comm.LONG_COMM_LINE_1, CHARINDEX('-', comm.LONG_COMM_LINE_1) - 2) LIKE ('%SHANNON%MURPHY%') OR
        LEFT(comm.LONG_COMM_LINE_1, CHARINDEX('-', comm.LONG_COMM_LINE_1) - 2) LIKE ('%MICHELLE%COLE%') OR
        LEFT(comm.LONG_COMM_LINE_1, CHARINDEX('-', comm.LONG_COMM_LINE_1) - 2) LIKE ('%ROGER%KONKEL%') OR
        LEFT(comm.LONG_COMM_LINE_1, CHARINDEX('-', comm.LONG_COMM_LINE_1) - 2) LIKE ('%DANIELLE%JONES%') OR
        LEFT(comm.LONG_COMM_LINE_1, CHARINDEX('-', comm.LONG_COMM_LINE_1) - 2) LIKE ('%SABRINA%WARNER%') OR
        LEFT(comm.LONG_COMM_LINE_1, CHARINDEX('-', comm.LONG_COMM_LINE_1) - 2) LIKE ('%CORINNE%DUBETZ%'))

I would like to define these people at the beginning of my query to be used later in a having statement or where statement, basically saying

HAVING (left(comm.LONG_COMM_LINE_1, CHARINDEX('-',comm.LONG_COMM_LINE_1) - 2) LIKE [anyone that I've already defined above]

I'm not sure if this is even possible or if so how I would even go about it. Any help would be immensely appreciated!

Answer 1

To expand on Alex K's comment, you can start out with the table variable:

DECLARE @People TABLE ([Name] VARCHAR(128)); 
INSERT @People VALUES ('%BRIAN%MILLER%'),('%LATEASHA%CALVERT%'),('%CHERITA%MARTIN%')

Now Let's say that I have a Member table and I need to find the members in this table based on your criteria, the query might look like this (I'm just grouping by name to conform to your example):

SELECT 
    M.[Name]
FROM 
    mem.Member M
GROUP BY 
    M.[Name]
HAVING 
    (EXISTS(SELECT 1 FROM @PEOPLE P WHERE M.Name LIKE P.Name))

More specifically for your example:

SELECT 
    comm.[LONG_COMM_LINE_1]
FROM 
    comm
GROUP BY 
    comm.[LONG_COMM_LINE_1]
HAVING 
    (EXISTS(SELECT 1 FROM @PEOPLE P WHERE  LEFT(comm.LONG_COMM_LINE_1, CHARINDEX('-', 
comm.LONG_COMM_LINE_1) - 2) LIKE P.Name))

Here is the docs on EXISTS for reference