Grouping multiple 'AND x not like y' In SQL statement

Question

I currently have an SQL statement that is too long for my program (I have a maximum number of character that I can use. I'm using sccm report). The problem is my SQL statement look like this:

Select distinct v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0, v_GS_ADD_REMOVE_PROGRAMS_64.Publisher0, v_GS_ADD_REMOVE_PROGRAMS_64.Version0
FROM v_GS_ADD_REMOVE_PROGRAMS_64 
JOIN  v_R_System ON v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID = v_R_System.ResourceID 
WHERE (v_R_System.Netbios_Name0 = @computername)

DisplayName0 NOT LIKE 'hpp%'
AND
DisplayName0 NOT LIKE 'Logitech SetPoint%'
AND
DisplayName0 NOT LIKE 'HP Document Manager%'
AND
DisplayName0 NOT LIKE 'HP Imaging Device Functions%'
AND
DisplayName0 NOT LIKE 'PyQt4 - PyQwt5%'

And it goes on and on for 20 pages. How can I minimize the amount of code this request contains? Is there a way to group all the displayName0 not like ?? with something like a NOT IN(value1, value2, ...)?

score 1 · Accepted Answer · answered Nov 19 '15 at 15:56

If you are OK without tailing % in you pattern you can replace it with:

SELECT ... WHERE DisplayName0 NOT IN ('hpp','Logitech SetPoint','HP Document Manager',...)

It would make it somehow shorter.

But it seems to me that proper solution would be to create [temp] table with all the names you need to filter against and then join it.

fez · Answer 2 · 2015-11-19T16:05:18.860

Could you store the values in a separate table and then reference it in your query like this?:

 SELECT DISTINCT v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0
    ,v_GS_ADD_REMOVE_PROGRAMS_64.Publisher0
    ,v_GS_ADD_REMOVE_PROGRAMS_64.Version0
FROM v_GS_ADD_REMOVE_PROGRAMS_64
JOIN v_R_System ON v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID = v_R_System.ResourceID
WHERE (v_R_System.Netbios_Name0 = @computername) DisplayName0 NOT IN (
        SELECT DisplayName0
        FROM < NewTableName >
        )

Grouping multiple 'AND x not like y' In SQL statement

2 Answers2