Database Filters-Getting pure data from a pool of data

Introduction

What do you do when you want to remove impurities from drinking water? Or what do you do when you want to remove tea leaves from your Tea.You use a strainer. What does a strainer do? It filters out the tea leaves or any other impurities from your tea and gives you a pure leaker. Similarly how do you extract relevant data from a pool of data in SQL Server? You use SQL Filters. SQL filters are text strings that you use to specify a subset of the data items in an internal or SQL database data type. For SQL database and internal data types, the filter is an SQL WHERE clause that provides a set of comparisons that must be true in order for a data item to be returned. Over the next few paragraphs we will discuss about the different aspects of filters, its advantage, and disadvantage if any and when we should use database filter.

SQL Server Training & Certification

• Detailed Coverage
• Best-in-class Content
• Prepared by Industry leaders
• Latest Technology Covered

Download Curriculum

Sql Server Database Filter

Let us start with the different types of filter operators. Below is a list of different types of filter operators. Once we check out on them, next we will see some practical examples of these operators.

 Now let us explore the above details with practical examples. For the examples we will be using the good old adventureworks database.

Is equal to

This filter returns any set of data which is equal to a particular value. The sample query is as follows.

select * from [dbo].[BookInventory] where TitleID=2

The above query returns values where TitleID is equal to 2.

The output is as follows

Is less than or equal to

This filter returns any set of data which is less than or equal to a particular value. The sample query is as follows

select * from [dbo].[BookInventory] where TitleID <=12

This returns all the values from the table BookInventory which are less than equal to 12.

The output is as follows.

Is like

This filter is used with a wild card character and returns all the value which satisfies a particular condition. Following is a sample query.

select * from [dbo].[BookInventory] where Title like 'P%'

This returns all the values where Titleid starts with P and then there can be any number of characters after it.

The output is as follows

Is not like

This filter is used with a wild card character and returns all the value which does not satisfies a particular condition. Following is a sample query.

select * from [dbo].[BookInventory] where Title  not like 'P%'

This returns all the values where P is not the first character in the Title column. Following is the output.

Is greater than or equal to

This filter returns any set of data which is greater than or equal to a particular value. The sample query is as follows

select * from [dbo].[BookInventory] where Quantity  >= 3

This returns all the rows where quantity is greater than or equal to a particular value. The output is as follows.

Is less than or equal to

This filter returns any set of data which is less than or equal to a particular value. The sample query is as follows

select * from [dbo].[BookInventory] where Quantity<=3

This returns all the rows where quantity is less than or equal to a particular value. The output is as follows.

Does not match

This filter returns any set of data which does not match with a particular value. The sample query is as follows

select * from [dbo].[BookInventory] where Quantity<>3

This returns all the rows where quantity does not match with the particular value 3. The output is as follows.

Is NULL

This filter returns any set of data which matches null value in a particular column. The sample query is as follows

select * from [HumanResources].[Employee] where OrganizationLevel is null

This returns all the rows where OrganizationLevel column value is null. The output is as follows.

Is not NULL

This filter returns any set of data which does not match null value in a particular column. The sample query is as follows

select * from [HumanResources].[Employee] where OrganizationLevel is not null

This returns all the rows where OrganizationLevel column value is not null. The output is as follows.

Please note, although we have used * wildcard characters in most of the query examples the filters will have equal effect if used with individual table columns as well.

SQL Server Training & Certification

• No cost for a Demo Class
• Industry Expert as your Trainer
• Available as per your schedule
• Customer Support Available

Enrol For a Free Demo Class

Advantages of SQL Server Filter

Filtering is a useful way to see only the data that you want displayed in SQL Server databases. You can use filters to display specific records in a form, report, query, or datasheet, or to print only certain records from a report, table, or query.

When to use filters

Filtering is a useful way to see only the data that you want displayed in Access databases. You can use filters to display specific records in a form, report, query, or datasheet, or to print only certain records from a report, table, or query.

SQL Server Training & Certification

• Personalized Free Consultation
• Access to Our Learning Management System
• Access to Our Course Curriculum
• Be a Part of Our Free Demo Class

Sign Up Now

Conclusion

Over the last few paragraphs, we have discussed about the different aspects of SQL Server database filter. This write-up gives you a glimpse of what we can achieve if we use filters in a SQL query. This also gives a brief idea when to use a filter and when not to. This write-up can be a beginning for a more detailed study of filter for any users in future.