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 SQL 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 SQL Wildcards along with the LIKE operator with examples. For this demo, we use the below-shown data.

Customers Table

SQL Like wildcard % Percentage Sign

The SQL 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%'
SQL Server LIKE Wildcard % Percentage Sign Operator

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

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

The following Sql Server Like operator 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 SQL 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%'
SQL Server Like Operator 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 SQL Like operator 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

SQL Server LIKE Wildcard [] 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

SQL Like Wildcard [^] NOT

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

Extra Wilcard Symbols

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

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.

Back to Categories SQL
SQL COUNT Function
SQL INNER JOIN

Related Topics

  • SQL Server Tutorial
  • How to Install SQL Server
  • Install AdventureWorks DB
  • Connect SQL with cmd utility
  • SQL Database
    • Create a Database
    • Attach Database
    • Detach Database
    • Get Database Names
    • Restore Database
    • Rename Database with Files
  • SQL Table
    • Available Data Types
    • Create Table
    • Add a New Column
    • Alter Table
    • Derived Table
    • Get Column Names From Table
    • Get Table Names in a DB
    • Global & Local Temp Table
    • Rename Column Name
    • Table Name and Column Name
    • Table Variable
  • SQL Basic Commands
    • ALIAS
    • BULK INSERT or BCP
    • DML, DDL, DCL & TCL Cmds
    • DELETE Statement
    • INSERT Statement
    • INSERT INTO SELECT
    • SELECT Statement
    • SELECT DISTINCT
    • SELECT INTO Statement
    • TRUNCATE Statement
    • UPDATE Statement
    • UPDATE from SELECT
    • Query Builder
  • SQL Clauses
    • Introduction to Clauses
    • GROUP BY Clause
    • Having Clause
    • ORDER BY Clause
    • TOP Clause
    • WHERE Clause
  • SQL Working with NULLS
    • COALESCE Function
    • IS NOT NULL
    • IS NULL Function
    • ISNULL
  • SQL Joins
    • Types of JOINS
    • List of Outer Joins
    • INNER JOIN
    • LEFT JOIN
    • RIGHT JOIN
    • FULL JOIN
    • SELF JOIN
    • CROSS JOIN
    • Cross Join Vs Inner Join
  • SQL Operators
    • AND & OR Operators
    • Arithmetic Operators
    • BETWEEN Operator
    • Comparison Operators
    • EXCEPT
    • EXISTS Operator
    • INTERSECT
    • IN Operator
    • LIKE Wildcard
    • NOT EXISTS Operator
    • NOT IN Operator
    • UNION
    • UNION ALL
  • SQL Control Flow
    • IIF Function
    • IF ELSE
    • ELSE IF
    • WHILE LOOP
    • BREAK
    • CONTINUE
    • GOTO Statement
    • CHOOSE
  • SQL Constraints & Indexes
    • Check Constraint
    • Clustered Index
    • Default Constraint
    • Non-Clustered Index
    • Primary Key
    • Foreign Key
    • Referential Integrity
    • Unique Constraint
    • Filtered Indexes
  • Advanced SQL Topics
    • CASE Statement
    • CDC Change Data Capture
    • CTE Example
    • MERGE Statement
    • PIVOT
    • Subquery
    • Table Partitioning
    • Table Partitioning using SSMS
    • TRY CATCH
    • TRANSACTIONS
    • Nested Transactions
    • ACID Properties
    • VIEWS
    • User Defined Functions
    • UNPIVOT
  • SQL Stored Procedures
    • Stored Procedures Introduction
    • INSERT Stored Procedure
    • SELECT Stored Procedure
    • UPDATE Stored Procedure
    • Input Parameters in SP
    • Output Parameters SP
    • Insert SP result into Temp Table
    • Return Values in SP
    • Useful System SPs
  • SQL Triggers
    • Introduction to Triggers
    • AFTER DELETE Triggers
    • AFTER INSERT Triggers
    • AFTER UPDATE Triggers
    • Instead Of DELETE Triggers
    • INSTEAD Of INSERT Triggers
    • INSTEAD of UPDATE Triggers
  • SQL Cursors
    • Static Cursor
    • Dynamic Cursor
    • FORWARD_ONLY Cursor
    • FAST_FORWARD Cursor
    • KEYSET Cursor
  • SQL Management Studio
    • Install Management Studio
    • Introduction to SSMS
    • Backup Database
    • Create SQL Server Login
    • Create SQL Server Roles
    • Create Windows Login
    • Maintenance Plan
    • Restore Database using BAK
    • SQL Server Login Error
    • Uninstall Management Studio
  • SQL Aggregate Functions
    • Introduction to Aggregate
    • AVG Function
    • CHECKSUM_AGG Function
    • COUNT Function
    • COUNT_BIG function
    • GROUPING Function
    • GROUPING_ID Function
    • MIN Function
    • MAX Function
    • STDEV Function
    • STDEVP Function
    • SUM Function
    • VAR Function
    • VARP Function
  • SQL Conversion Functions
    • CAST Function
    • TRY CAST
    • CONVERT
    • TRY CONVERT
    • PARSE Function
    • TRY_PARSE Function
  • SQL Date Functions
    • Introduction to Dates
    • GETDATE Function
    • GETUTCDATE Function
    • DAY Function
    • MONTH Function
    • YEAR Function
    • DATEADD
    • DATEDIFF
    • DATENAME
    • DATEPART
    • EOMONTH
    • DATEFROMPARTS
    • DATETIMEFROMPARTS
    • DATETIME2FROMPARTS
    • DATETIMEOFFESETFROMPARTS
    • SMALLDATETIMEFROMPARTS
    • TIMEFROMPARTS
    • TODATETIMEOFFSET
    • SWITCHOFFSET
    • SYSDATETIMEOFFSET
    • SYSUTCDATETIME Function
    • SYSDATETIME Function
    • DATEFIRST
    • @@DATEFIRST
    • @@LANGUAGE
    • SET DATEFORMAT
    • SET LANGUAGE
    • sp_helplanguage
    • CURRENT_TIMESTAMP
    • ISDATE
  • SQL Math Functions
    • Introduction to Math Functions
    • ABS Function
    • COS Function
    • ACOS Function
    • SIN Function
    • ASIN Function
    • ATAN Function
    • TAN Function
    • CEILING
    • FLOOR Function
    • EXP Function
    • LOG Function
    • LOG10 Function
    • POWER Function
    • RAND Function
    • RADIANS Function
    • SIGN Function
    • SQRT Function
  • SQL Ranking Functions
    • Introduction to Ranking
    • RANK Function
    • DENSE_RANK Function
    • PERCENT_RANK Function
    • NTILE Function
  • SQL String Functions
    • Introduction to Strings
    • ASCII Function
    • CHAR Function
    • CONCAT Function
    • CONCAT_WS Function
    • CHARINDEX Function
    • DIFFERENCE Function
    • FORMAT Function
    • LEFT Function
    • LEN Function
    • LTRIM function
    • NCHAR Function
    • PATINDEX Function
    • QUOTENAME Function
    • REPLICATE Function
    • REPLACE Function
    • REVERSE Function
    • RTRIM Function
    • SPACE Function
    • SOUNDEX function
    • STR Function
    • STRING_AGG Function
    • STRING_ESCAPE Function
    • STRING_SPLIT Function
    • STUFF Function
    • SUBSTRING Function
    • TRANSLATE Function
    • TRIM Function
    • UPPER & LOWER Functions
    • UNICODE Function
  • SQL Statistical Functions
    • Introduce Statistical Functions
    • @@CONNECTIONS
    • @@CPU_BUSY
    • @@IDLE
    • @@IO_BUSY
    • @@PACK_SENT
    • @@PACKET_ERRORS
    • @@PACK_RECEIVED
    • @@TIMETICKS
    • @@TOTAL_ERRORS
    • @@TOTAL_READ
    • @@TOTAL_WRITE
  • SQL System Functions
    • Introduce System Functions
    • @@IDENTITY
    • ERROR LINE
    • ERROR MESSAGE
    • ERROR NUMBER
    • ERROR SEVERITY
    • ERROR STATE
    • HOST_ID
    • HOST_NAME
    • ISNULL
    • ISNUMERIC Function
  • SQL Analytics Functions
    • FIRST_VALUE
    • LAST_VALUE
    • CUME_DIST
    • LAG Function
    • LEAD Function
    • PERCENTILE_DISC
    • PERCENTILE_CONT
  • SQL Config Functions
    • Introduction to Configurations
    • @@LANGID
    • @@LANGUAGE
    • @@MAX_CONNECTIONS
    • @@MAX_PRECISION
    • @@SERVICENAME
    • @@SERVERNAME
    • @@SPID
    • @@VERSION
  • SQL Working on XML
    • FOR XML AUTO
    • FOR XML PATH
    • FOR XML RAW
  • SQL Interview Questions
    • Calculate Running Total
    • Create ODBC Connection
    • Check if a Table exists
    • Configure SQL Database Mail
    • Concatenate Rows into String
    • Count Records in a Group
    • CTE Vs Temp Vs Derived tables
    • DATE Format
    • DATEPART Vs DATENAME
    • Delete Duplicate Rows
    • Extract Domain From Email
    • Export Data From SQL to CSV
    • Find the Table Dependencies
    • Find Nth Highest Salary
    • Find all Tables that Contain Specific Column Name
    • Get Last Record from each Group
    • IDENTITY INSERT
    • Identity Value Jumps after restarting SQL Server
    • Insert Images into SQL Server
    • Replace a String
    • Reverse String
    • Return Date Part Only from a Datetime datatype
    • Rows with Max Column Value
    • Swap Column Values
    • Standard Date Time Format Strings
    • Select First Row in each group
    • Select All If Parameter is Empty or NULL
    • Top N Records from each Group
    • UNION Vs UNION ALL
  Copyright © 2023. All Rights Reserved.
Home | About | Contact | Privacy Policy