RnewYear2022 RnewYear2022

- 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

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

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.


     user

    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.


Comments

Related Courses

Trending Courses

salesforce

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53
salesforce

Upcoming Class

2 days 04 Feb 2023

salesforce

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
salesforce

Upcoming Class

1 day 03 Feb 2023

salesforce

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
salesforce

Upcoming Class

1 day 03 Feb 2023

salesforce

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
salesforce

Upcoming Class

1 day 03 Feb 2023

salesforce

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
salesforce

Upcoming Class

1 day 03 Feb 2023

salesforce

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
salesforce

Upcoming Class

1 day 03 Feb 2023

salesforce

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
salesforce

Upcoming Class

1 day 03 Feb 2023

salesforce

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
salesforce

Upcoming Class

1 day 03 Feb 2023

salesforce

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
salesforce

Upcoming Class

15 days 17 Feb 2023

salesforce

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
salesforce

Upcoming Class

1 day 03 Feb 2023

salesforce

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
salesforce

Upcoming Class

22 days 24 Feb 2023

salesforce

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
salesforce

Upcoming Class

1 day 03 Feb 2023

Interviews