How to filter my data in the Query

Question

I have a set of data, some 18000 posts.

They are either "Server" or "Workstations".
If they are servers, the OperatingSystem contains the word server.
If they are Workstations, the OperatingSystem DOES NOT contains the word server.

I really need to be able to filter in 3 different values:

1. All - OperatingSystem LIKE '%'
2. Servers - OperatingSystem LIKE '%SERVER%'
3. Workstations - OperatingSystem NOT LIKE '%SERVER%'

These filters work but how do I incorporate them in my rather simple query, so that I can control the filtered results with a variable?

My very simple test query:

DECLARE @OSTYPE NVARCHAR(15)
SET     @OSTYPE     =   'SERVER'

SELECT  distinct OperatingSystem

FROM    v_Some_Odd_View

order by OperatingSystem

I have tried nested IF and CASE in the WHERE clause. But hours of testing and googling didn't help me and I'm quite sure that I'm missing something minor and completely obvious.

My unfiltered result:

Microsoft Windows 10 Enterprise
Microsoft Windows 10 Pro
Microsoft Windows 7 Enterprise
Microsoft Windows Server 2008 R2 Enterprise
Microsoft Windows Server 2008 R2 Standard
Microsoft Windows Server 2012 R2 Datacenter
Microsoft Windows Server 2012 R2 Standard
Microsoft Windows Server 2016 Datacenter
Microsoft Windows Server 2016 Standard
Microsoft Windows Server 2019 Datacenter
Microsoft Windows Server 2019 Standard
Microsoft® Windows Server® 2008 Enterprise
Microsoft® Windows Server® 2008 Standard

My simple Query for SERVER OS, only:

DECLARE @OSTYPE NVARCHAR(15)
SET     @OSTYPE     =   'SERVER'

SELECT  distinct OperatingSystem

FROM    v_Some_Odd_View
Where   OperatingSystem Like '%' + @OSTYPE + '%'

order by OperatingSystem

Maybe I'm Googeling with the wrong words. Maybe I'm missing the most obvious thing in the world. Please point me in the right direction.

Answer 1

It is not clear to me how you want the search to work, but maybe this:

WHERE (@OSTYPE = 'ALL') OR
       (@OSTYPE = 'SERVER' AND OperatingSystem LIKE '%SERVER%') OR
       (@OSTYPE = 'WORKSTATION' AND OperatingSystem NOT LIKE '%SERVER%')

Answer 2

Hi @JGaard ,

Welcome to Microsoft Q&A!

Please also refer to below:

DECLARE    @OSTYPE NVARCHAR(15)  
SET        @OSTYPE= 'SERVER'  
  
DECLARE @sql varchar(max)  
   
SET @sql= 'SELECT distinct OperatingSystem FROM v_Some_Odd_View WHERE ' +   
case when @OSTYPE = 'SERVER' then 'OperatingSystem LIKE ''%SERVER%'''  
    when @OSTYPE = 'WORKSTATION' then 'OperatingSystem NOT LIKE ''%SERVER%'''  
    when @OSTYPE = 'ALL' THEN  'OperatingSystem LIKE ''%''' END  
+' order by OperatingSystem'  
     
EXEC (@sql)  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.