Skip to content
Tutorial Gateway
  • C
  • C#
  • Python
  • SQL
  • Java
  • JS
  • MySQL
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs
    • Search
SQL LIKE Wildcard 5

SQL LIKE

The SQL Server LIKE Operator extracts the records whose values match the specified pattern. The Wildcard should use along with the LIKE operator. For example, If you forgot the Spelling of a Company or Product, you can use the operator and wildcard to retrieve the required information.

Here Wildcards will be helpful to replace the missing word. The SQL Server supports the following LIKE Wildcards.

WildcardsDescription
%It represents Zero or more characters.
_It represents exactly one character.
[Char list OR Range]Selects the records which are exactly matching with the characters or range of characters present in the square brackets
[^Char list OR Range]Selects the records which are not matching with the characters or range of characters present in the square brackets

Let us see how to use the Wildcards along with LIKE with examples. For this demo, we use the below-shown data.

Customers Table

SQL Like wildcard % Percentage Sign

The Like Wildcard percentage sign (%) represents zero or more characters. For example, the following % query returns all the Customers whose First name starts with Letter J.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [FirstName] LIKE N'J%'
% Percentage Sign

This example displays the Customers whose Occupation ends with Letter l.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [Occupation] LIKE N'%l'
SQL Like Percentage Sign Example 2

The following Sql Server Like wildcard query selects the Customers Whose Occupations start with Letter M and ends with t.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [Occupation] LIKE N'M%t'
Using Percentage to display text start and end with characters 4

The below Server query returns the Customers Whose Occupations contain Ma at any position.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [Occupation] LIKE N'%Ma%'
% in the middle

_ Wildcard Underscore Sign

The Underscore sign (_) represents a Single character.

The following Like Operators _ wildcard query selects all the Customers Whose First name has a Second Letter o.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [FirstName] LIKE N'_o%'
underscore sign

This Like wildcard query displays the Customers whose Yearly income contains 00 at the second and third positions.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [YearlyIncome] LIKE N'_00%'
Like Wildcard _ and % sign

This query displays the Customers whose First name has at least four characters.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [FirstName] LIKE N'_%_%_%_%'
underscore and percentage

The following Like wildcard _ query returns the Customers whose Yearly income starts with 8 and ends with 0. Here, we used three _ sign means, which will accept three numbers or words between 8 and 0.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [YearlyIncome] LIKE N'8___0'
multiple underscores

Using [] Character List

Use LIke [] (character list) wildcard to search for multiple items. For example, it returns all the Customers in the Customers table Whose First name starts with either the Letter C or J.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [FirstName] LIKE N'[CJ]'
Using SQL Like [] Character List

This query returns the Customers whose First name does not start with either C or J.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [FirstName] LIKE N'[^CJ]%'
Name doesn't Start with either of the two alphabets

SQL Like Wildcard Example using Character List Range

Use this character range wildcard Like operator to search for a series of alphabets etc. This query returns all Customers from the Customers table whose Last name starts with any Letter between M and Y.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [LastName] LIKE N'[M-Y]%'
Using Character List Range

Like Wildcard [^]

It acts as the NOT operator.

The following query displays the Customers whose Last name doesn’t start with any Letter between M and Y.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [LastName] LIKE N'[^M-Y]%'
^ Example

SQL NOT LIKE Operator Example

This Like operator allows us to use the NOT Keyword as well. For example, the below query shows the Customers whose First name doesn’t start with J.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [FirstName] NOT LIKE N'J%'
SQL Not Like Example

You can also write the above statement Using [^Character List] Wildcard. This statement shows the Customers whose First name doesn’t start with either Letters C or J.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [FirstName] NOT LIKE N'[CJ]%'
Doesn't start with either Letters

The below query returns the Customer’s table records whose Last name doesn’t start with Letters between M or Z.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [LastName] NOT LIKE N'[M-Z]%'
Not starts with two Alphabets

This statement selects the Customers from the Customers table Whose First name doesn’t have o in the second place.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [FirstName] NOT LIKE N'_o%'
Not Underscore and Percentage

You can also use the Like Wildcard characters %, _ as the literal characters too. However, you have to do some extra work to achieve this. The following table will show you some examples.

SymbolsOutput
’10[%]’10%
[_]n_n
‘[-abcd]’-, a, b, c, d
‘[[]’[
‘]’]
‘abc[_]d%’abc_d, or abc_def etc
Back to Categories SQL
SQL COUNT Function
SQL INNER JOIN

Related Topics

  • Management Studio Intro
  • Connect with cmd utility
  • Attach Database
  • Detach Database
  • Backup Database
  • Restore Database
  • Restore Database using BAK
  • Rename Database with Files
  • Get Database Names
  • Create Table
  • Alter Table
  • Get Table Names in a DB
  • Global & Local Temp Table
  • Table Variable
  • Derived Table
  • DML, DDL, DCL & TCL Cmds
  • Query Builder
  • ALIAS
  • SELECT
  • DISTINCT
  • SELECT INTO
  • INSERT
  • INSERT INTO SELECT
  • BULK INSERT or BCP
  • UPDATE
  • UPDATE from SELECT
  • DELETE
  • TRUNCATE
  • CASE
  • MERGE
  • Subquery
  • CTE Example
  • PIVOT
  • UNPIVOT
  • TOP Clause
  • WHERE
  • ORDER BY
  • GROUP BY
  • Having
  • Primary Key
  • Foreign Key
  • Referential Integrity
  • Check Constraint
  • Unique Constraint
  • Default Constraint
  • Clustered Index
  • Non Clustered Index
  • Filtered Indexes
  • COALESCE Function
  • IS NOT NULL
  • IS NULL Function
  • ISNULL
  • Types of JOINS
  • CROSS JOIN
  • FULL JOIN
  • SELF JOIN
  • Outer Joins
  • LEFT JOIN
  • RIGHT JOIN
  • AND & OR Operators
  • Arithmetic Operators
  • BETWEEN
  • Comparison Operators
  • LIKE
  • EXCEPT
  • EXISTS Operator
  • NOT EXISTS Operator
  • INTERSECT
  • IN Operator
  • UNION
  • UNION ALL
  • IF ELSE
  • ELSE IF
  • WHILE LOOP
  • BREAK
  • CONTINUE
  • GOTO Statement
  • IIF Function
  • CHOOSE
  • Change Data Capture
  • Table Partitioning
  • TRY CATCH
  • VIEWS
  • User Defined Functions
  • Stored Procedures
  • Useful System Stored Procedures
  • Triggers
  • Cursors
  • TRANSACTIONS
  • ACID Properties
  • Maintenance Plan
  • Ranking Functions
  • Aggregate Functions
  • Date Functions
  • Mathematical Functions
  • String Functions
  • CAST Function
  • TRY CAST
  • CONVERT
  • TRY CONVERT
  • PARSE Function
  • TRY_PARSE Function
  • Calculate Running Total
  • Find Nth Highest Salary
  • Reverse String
  Copyright © 2022. All Rights Reserved.
Home | About | Contact | Privacy Policy