How to filter my data in the Query

JGaard 21 Reputation points
2021-10-04T19:34:50.107+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,317 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 106.5K Reputation points
    2021-10-04T21:43:08.923+00:00

    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%')
    
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. MelissaMa-MSFT 24,186 Reputation points
    2021-10-05T02:35:06.05+00:00

    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.