Black Friday Deal : Up to 40% OFF! + 2 free self-paced courses + Free Ebook - SCHEDULE CALL

Select Course
Blog
Corporate Training

+1 202 599 3842

(4.8/5 ) | 1.5K+ Ratings

- SQL Server Blogs -

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.

Control Name	Description
Is equal to	Returns attribute values that are exactly the same as the specified criteria. For example, to filter on Mountain-100, you must type Mountain-100.
Is not equal to	Returns attribute values that are not exactly the same as the specified criteria. The filter criteria must be exactly the same as the attribute value you want to omit from the results. For example, to omit results that match Mountain-100, you must type Mountain-100. Note: When you apply a filter condition with an "Is not equal" clause on an attribute, a member for which the attribute is NULL will pass the filter condition and be returned if SET ANSI_NULLS is set to ON in your database settings. To stop this behavior, turn SET ANSI_NULLS to OFF in your database settings. When SET ANSI_NULLS is set to OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL, with the result that the member would not pass the "Is not equal" clause. For more information, see SET ANSI_NULLS (Transact-SQL).
Is like	Uses the LIKE operator from Transact-SQL to filter results. For more information, see LIKE (Transact-SQL) in SQL Server Books Online.
Is not like	Uses the NOT operator from Transact-SQL to filter results. For more information, see NOT (Transact-SQL) in SQL Server Books Online.
Is greater than	Returns attribute values that are greater than the specified criteria. For example, to return attribute values that start with a letter greater than F, type F.
Is less than	Returns attribute values that are less than the specified criteria. For example, to return attribute values that start with a letter less than F, type F.
Is greater than or equal to	Returns attribute values that are greater than or equal to the specified criteria. For example, to return attribute values that start with the number 3 or greater, type 3.
Is less than or equal to	Returns attribute values that are less than or equal to the specified criteria. For example, to return attribute values that start with the number 3 or less, type 3.
Matches	Uses a fuzzy lookup index to filter results. Use the Similarity Level field to specify how closely the attribute values must match the specified filter criteria (with a default of 30%). Select one of the following in the Algorithm list box: Levenshtein: A distance that is based upon the number of edits (for example, adds or deletions) that it takes for one string to match another. This is the default. Does not require any additional parameters. Jaccard: An index that works best when trying to match multiple strings. This search supports an additional parameter of containment bias (see below).Jaro-Winkler: A distance that is best used for finding duplicate person names. This method returns more results than any other method. Does not support containment bias. Longest Common Subsequence: Works based upon a subsequence in which the letters in a pattern appear in order, although they can be separated (for example, "MSR" is a subsequence of "MaSteR"). This search supports an additional parameter of containment bias (see below). Note: For the Jaccard or Longest Common Subsequence algorithm add a Containment Bias. This is a length threshold that is provided in a decimal percentage between 0 and 1, with a default of .62. A lower threshold will increase the number of possible matches returned.
Does not match	Uses a fuzzy lookup index to filter results. Use the Similarity Level field to specify how closely the attribute values must not match the specified filter criteria.
Contains pattern	Uses .NET Framework regular expressions to filter results on a specified pattern. For more information about regular expressions, see Regular Expression Language Elements in the MSDN Library.
Does not contain pattern	Uses the .NET Framework regular expressions to filter results that do not match a specified pattern. For more information about regular expressions, see Regular Expression Language Elements in the MSDN Library.
Is NULL	Returns attribute values that are null. The Criteria field disables when you select the Is NULL operator.
Is not NULL	Returns attribute values that are not null. The Criteria field disables when you select the Is not NULL operator.

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

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.

Sanchayan Banerjee

I love to learn new things and also like sharing my knowledge with others. As an experienced IT Professional I like to update myself constantly with new and upcoming technologies. The database management system is one of my favorite subjects which I constantly explore.