Our Support: During the COVID-19 outbreak, we request learners to CALL US for Special Discounts!

- SQL Server Blogs -

How to Use Like Operator in SQL Server?



Introduction

SQL is the common query language to work with databases. When you design a report or using BI or any particular reporting tool, the software is running SQL query behind the scenes and always returns the selected data. When you want to select the desired data based on specific criteria, the WHERE clause is commonly used by the programmer to complete this need. It gives you an option to query specific rows to fetch the desired data instead of querying an entire table.

Read: Introduction to Stored Procedures and its benefits

Today, in this blog post, we will discuss how to use SQL LIKE operator in the SQL server along with regular expressions to manipulate text. We will also discuss the basic syntax of the LIKE clause and how to use it with wildcard characters.

We will be using character sets and repetition expressions to create the most flexible matching patterns. We will also learn to use  LIKE operators in different ways. In later sections, we will discuss the LIKE clause with some examples for a deeper understanding of the concept. Also, go for a demo class to understand what SQL is and if a certification course will help you develop the necessary skills.

SQL Server Training & Certification

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

What is SQL LIKE Operator?

The SQL LIKE query is a logical operator to check if a specified string matches the desired pattern or not. A pattern may involve regular expressions or wildcard characters etc. The LIKE operator in SQL can be used along SELECT, UPDATE, WHERE, or DELETE statements, etc. to filter rows based on matching patterns. HERE is the basic syntax of SQL LIKE operator in SQL Server.

column | expression LIKE pattern [ESCAPE escape_character]

SQL Like operator is mostly used for filtering addresses where you know only a selected part of the address and want to fetch the complete address using SQL LIKE operator. And wildcards are the most attractive resource here to evaluate the string even better based on requirements.

Read: Different Type of SQL Joins

What is the Pattern?

The pattern is a sequence of characters you want to search in a given expression or column. It may include the following wild characters as given below. Wildcard helps using Like Operator in a more flexible and excellent manner.

The Percent (%) Wildcard A string of zero or more characters
The Underscore (_) Wildcard It may be any single character
The [list of characters] Wildcard A single character within a specified set
The [Character – Character] Wildcard A single character within a specified range
The [^] Wildcard A single character not available within a specified range

What is Escape Character?

The Escape characters in SQL server instruct LIKE operators  to manage wildcards as regular expressions. There is no default value for escape characters, and it evaluates only one character at one time. The LIKE operator returns TRUE if an expression matches the specified pattern. To reverse the result, you should add not an operator  with the LIKE clause. Here is the syntax of how to negate the result using NOT operator in the same query.

Read: How to Add A New Column to a Table in SQL?

column | expression NOT LIKE pattern [ESCAPE escape_character]

What is a regular expression?

A regular expression is a sequence of characters or pattern that defines how data can appear in an expression. Usually, SQL databases contain different types of data like strings, numeric data, or images, etc. SQL stores the unstructured data too. And SQL queries usually return data based on regular expressions. One common example is validating a credit card number or phone number etc. Another example could be validating numeric data against a given sequence.

SQL server database implementations provide various built-in regular expressions support. Let us see how to use regular expressions in a SQL query. Take an example where we need to display all the records whose name starts with the alphabet “P.” The syntax can be written as given below.

Read: What Is A Data Warehouse, And Why You Need It?

SELECT * FROM Survey WHERE RespondentFullName LIKE 'p%'

Regular expressions are generally named as regex in SQL, and you can use any of them as per the convenience. There are certain cases when sensitivity has to be considered within regex processing. You should focus on sensitivity properties there to compute the final output.

How to use SQL LIKE operator with wildcard characters?

In this section, we will discuss how to use SQL LIKE operator with different wildcard characters as discussed in the table above.

SQL LIKE clause with “%” wildcard

The following SQL query returns first top 10 rows where the last name starts with the letter ‘A’ and rest of the string is unknown. You can query the entire database table as well if required.

SELECT TOP 10 *
FROM Person.Person
WHERE firstname LIKE 'A%';

The “%” wildcard can be used on either side of the keyword. Keywords are the part of a longer text string or word. When “%” wildcard is used at the left side of the keyword, it means that keyword is on the ending side of the word. When it is used on the right side of the keyword, it means the keyword is at the beginning of another word.

Learn SQL Server in the Easiest Way

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

SQL LIKE clause with “_” wildcard

The underscore wildcard is used for matching a single character only. The following SQL query returns the phone number whose area code starts with 7 and ends with 8 in the “phone-number” column. We have also used “%” wildcard here as we are not concerned about the rest of the string.

SELECT p.FirstName, 
       p.LastName, 
       PhoneNumber
FROM Person.PersonPhone AS ph
     INNER JOIN Person.Person AS p ON ph.BusinessEntityID = p.BusinessEntityID
WHERE ph.PhoneNumber LIKE '7_8%'
ORDER BY p.LastName;

SQL LIKE clause with “[Square bracket]” wildcard

Square Bracket wildcard is used to identify multiple single characters into that particular position. Let us say you have to find out rows where the third character in the first-name column starts with either “I” or “K.” The following query will look for “I” first then it will look for “K.” Let execute the SQL query first.

SELECT p.FirstName, 
       p.LastName, 
       PhoneNumber
FROM Person.PersonPhone AS ph
     INNER JOIN Person.Person AS p ON ph.BusinessEntityID = p.BusinessEntityID
WHERE ph.PhoneNumber LIKE '7_8%' and p.lastname like 'Ba[ik]%'
ORDER BY p.LastName;

Let us see how to rewrite the same query using the “OR” operator.

Read: MSBI Interview Questions & Answers for Fresher, Experienced

SELECT p.FirstName, 
       p.LastName, 
       PhoneNumber
FROM Person.PersonPhone AS ph
     INNER JOIN Person.Person AS p ON ph.BusinessEntityID = p.BusinessEntityID
WHERE ph.PhoneNumber LIKE '7_8%' and (p.lastname like 'Bai%' or p.lastname like 'Bak%')
ORDER BY p.LastName;

SQL LIKE clause with “^” wildcard

To negate the result, we may use “^” wildcard in the SQL Server along with the SQL LIKE clause. For example, you want to display all the rows that don’t have letters A to D in the first character of their last names. It becomes a NOT condition here. Here is the example for your reference.

SELECT p.FirstName, 
       p.LastName
FROM Person.Person p
WHERE LastName LIKE '[^a-d]%'
ORDER BY p.lastname;

The output for this query is rows that don’t contain characters from A to D in the first position.

Read: Top 100 SQL Interview Questions and Answers

Learn using SQL LIKE operator with a CASE statement

The following query pulls out the contacts where phone numbers are formatted like three-three-four digits including dashes in between. The pattern is further compared with the phone-number columns to fetch the desired result. Here, the CASE statement is used to evaluate the specific pattern to drive the phone category type.

SELECT p.FirstName, 
       p.LastName, 
     PhoneNumber,
       CASE WHEN ph.PhoneNumber LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' then 'Domestic Phone Number'
     ELSE 'International Phone number'
     END PhoneNumber
FROM Person.PersonPhone AS ph
     INNER JOIN Person.Person AS p ON ph.BusinessEntityID = p.BusinessEntityID
ORDER BY p.LastName;

Phone numbers are either defined as domestic or international. The phone number column is evaluated here with SQL LIKE operator using square brackets. Now the string is evaluated from zero to nine from the first character to the last character and the matched patterns are listed below as the final output.

Read: How To Become SQL Certified Professional To Grow Your Career?

SQL Like Operator with dynamic SQL

The following SQL statement returns rows where the last name matches the pattern ‘barb.’ Here, the pattern is created dynamically and matched against the given expression. Here is the query for your reference.

DECLARE @ELastName VARCHAR(20)= 'Barb';
SELECT p.FirstName, 
       p.LastName, 
       a.City
FROM Person.Person p
     JOIN Person.Address a ON p.BusinessEntityID = a.AddressID
WHERE p.LastName LIKE '%'[email protected]+'%';

Keep in mind that CHAR injects trailing blacks by default based on the length of the field. You should use RTRIM operator here to manage the training blanks especially when you are using the char data type. In the following SQL statement, the first name and the last name is of char data type, and we have used RTRIM function here for suppressing the trailing blanks.

DECLARE @ELastName CHAR(20)= 'Barb';
SELECT p.FirstName, 
       p.LastName, 
       a.City
FROM Person.Person p
     JOIN Person.Address a ON p.BusinessEntityID = a.AddressID
WHERE p.LastName LIKE '%'+RTRIM(@ELastName)+'%';

Sign up for an online JanBask Training course right now!

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

Learn using SQL Like operator with IF statement

In the following SQL statement, input values are evaluated for a specific pattern in the conditional clause using the IF statement. The input string is evaluated for a given pattern using SQL LIKE operator with wildcards expressions and return a valid output data. Here is the sample query for your reference.

DECLARE @RuleName NVARCHAR(MAX)= 'SQL Sever 2019 CTP is available for preview';
IF @RuleName LIKE 'SQL Sever [0-9]% CTP is available for preview'
    PRINT 'valid input good!';
    ELSE
    PRINT 'not a valid good!';

Summary:

As discussed in the blog, SQL LIKE in SQL is a great searching technique for matching a string of characters with a specified pattern especially when we are not sure of what you are looking for exactly. With the usage of wildcard characters, we can make the SQL LIKE operator more flexible.

I hope you enjoyed reading this blog on SQL LIKEin SQL Server. To know more on practical concepts of SQL operators, you should join our SQL certification program at JanBask Training and get ready to expand your knowledge base with us.

Read: What is Update Query in SQL? How to Update (Column Name, Table, Statement, Values)

SQL Tutorial Overview


    Janbask Training

    A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.


Comments

Trending Courses

AWS

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

Upcoming Class

4 days 13 Aug 2020

DevOps

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

Upcoming Class

1 day 10 Aug 2020

Data Science

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

Upcoming Class

8 days 17 Aug 2020

Hadoop

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

Upcoming Class

6 days 15 Aug 2020

Salesforce

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

Upcoming Class

1 day 10 Aug 2020

QA

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

Upcoming Class

8 days 17 Aug 2020

Business Analyst

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

Upcoming Class

5 days 14 Aug 2020

MS SQL Server

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

Upcoming Class

5 days 14 Aug 2020

Python

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

Upcoming Class

8 days 17 Aug 2020

Artificial Intelligence

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

Upcoming Class

4 days 13 Aug 2020

Machine Learning

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

Upcoming Class

7 days 16 Aug 2020

Tableau

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

Upcoming Class

1 day 10 Aug 2020

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews