Skip to content

SqlSkull

SQL Server | SSRS | SSIS | Power BI | DAX
  • Home
  • About Us
  • Microsoft SQL Server
    • SQL Basics Concepts
    • SQL Advance Concepts
    • SQL Interview Q & A
    • SQL Server Tricks & Tips
  • SSRS
  • SSIS
  • POWER BI
  • Python with ML
  • Microsoft Azure
  • HTML Color picker

SqlSkull

SQL Server | SSRS | SSIS | Power BI | DAX
  • Home
  • About Us
  • Microsoft SQL Server
    • SQL Basics Concepts
    • SQL Advance Concepts
    • SQL Interview Q & A
    • SQL Server Tricks & Tips
  • SSRS
  • SSIS
  • POWER BI
  • Python with ML
  • Microsoft Azure
  • HTML Color picker
Home » SQL LIKE CLAUSE

SQL LIKE CLAUSE

  • by Pradeep Raturi
  • SQL Basics

The SQL LIKE clause is used to match a value to a similar values using wildcard operators. It is used with Where Clause.





There are two type of wildcards used in conjunction with the LIKE operator.

  • The percent sign (%) that is  used to represent zero , one, or multiple characters.
  • The underscore (_) that is used to represent a single number or character.

Following are the list of Wild Card operators in SQL.

%  :   It represents zero or more characters.

[ ]  :  It represents a single character within the brackets.

_    :  It represents a single character.

–    :  It represents a range of characters.

^    :  It is used with bracket [] , and represents any character that is not in the bracket.

Lets look at an example of LIKE clause with wild card operators.

Like  ‘T%’ finds the CusotmersId that start with ‘T’

Select OrderId ,CustomerId ,OrderDate from Orders
Where CustomerID LIKE 'T%'

like in sql

Like  ‘%T’  finds the CusotmersId that end with ‘T’

Select OrderId ,CustomerId ,OrderDate from Orders
Where CustomerID LIKE '%T'

Like  ‘__T%’ finds the cusotmersId that have any character in first and second position and  ‘T’ in third position

Select OrderId ,CustomerId ,OrderDate from Orders
Where CustomerID LIKE '__T%'

Like  ‘W__T_%’ finds the CusotmersId having 5 digits , start with ‘W’ and any character in second and third position and ‘T ‘ in the fourth position 

Select OrderId ,CustomerId ,OrderDate from Orders
Where CustomerID LIKE 'W__T_%'




 Like  ‘%EL%’ finds CusotmersId that have ‘EL’ in any position

Select OrderId ,CustomerId ,OrderDate from Orders
Where CustomerID LIKE '%EL%'

 Like  ‘AN%’  –  It finds employee LastName that end with ‘AN’

Select EmployeeID , LastName ,FirstName from Employees
Where LastName LIKE '%AN'

 Like  ‘_[UE]’  –  It finds employee LastName that start with any character and second character can be either ‘E’ or ‘U’ only

Select EmployeeID , LastName ,FirstName from Employees
Where LastName LIKE '_[ue]%'

Like  ‘_[^UE]’  finds employee LastName that start with any character and character in second position can not be ‘E’ and ‘U’ 




Select EmployeeID , LastName ,FirstName from Employees
Where LastName LIKE '_[^ue]%'

Like  ‘__[A-L]’ finds employee LastName have any character in first and second position and  third position character can be any character between A to L

Select EmployeeID, LastName, FirstName from Employees
Where LastName LIKE '__[A-L]%'

Like  ‘[1-5,A-C]’ finds employee Address that start with any character between 1-5  or A-C 

Select EmployeeID ,Address from Employees
Where Address LIKE '[1-5,a-c]%'

 




 1,263 total views,  2 views today

Share this:

  • Click to share on Facebook (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on WhatsApp (Opens in new window)
  • Click to share on Reddit (Opens in new window)

Related

Tags:how to match pattern in sql serverLike %LIKE operator in Sqllike _partial search in sql serverpattern matching in sql serverSQLSQL DatabaseSql like clausesql like operatorsql pattern matchSQL Serversql server like clauseSQL Server TutorialsSQL Server Tutorials By Pradeep Raturisql server wildcardSQL Tutorialssql wild card operatorthe like clause in sql

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

previousUSER DEFINED FUNCTIONS
nextSchedule a Job in SQL
Generic selectors
Exact matches only
Search in title
Search in content

SQL Server Bascis

  • SQL Basics Concepts
    • SQL
    • CREATE DATABASE
    • SQL Attach Database
    • SQL Detach Database
    • SQL Server NCHAR
    • SQL Server CHAR
    • SQL Server CHAR VS NCHAR
    • SQL SERVER NVARCHAR
    • SQL Server VARCHAR VS NVARCHAR
    • SQL Server Date
    • SQL CREATE TABLE
    • SQL ALTER TABLE
    • Temporary table / temp table in SQL Server
    • SQL Server SELECT
    • SQL SELECT TOP
    • SQL SELECT INTO SELECT
    • SQL INSERT INTO SELECT
    • SQL SERVER UPDATE
    • Update from Select Statement in SQL
    • SQL Server DELETE
    • SQL Delete TOP
    • SQL TRUNCATE TABLE
    • Truncate VS Delete statements in SQL Server
    • SQL DROP TABLE
    • SQL Server ORDER BY
    • SQL HAVING CLAUSE
    • SQL SERVER WHERE
    • SQL SERVER DISTINCT
    • Table Value Constructor
    • SQL Server Computed Columns
    • SQL NULLIF
    • SQL IS NULL
    • SQL IS NOT NULL
    • NULL VS BLANK
    • SQL EXISTS Operator
    • SQL Between Operator
    • SQL LIKE CLAUSE
    • SQL SERVER EXCEPT
    • SQL SERVER INTERSECT
    • SQL UNION ALL
    • SQL UNION
    • SQL Primary Key constraint
    • SQL FOREIGN KEY Constraint
    • Cascading referential integrity in SQL Server
    • SQL Unique Key Constraint
    • SQL Server Composite Key
    • Primary Key VS Composite Key
    • SQL Server CHECK Constraint
    • SQL NOT NULL Constraint
    • SQL Default Constraint
    • SQL Server JOINS
      • SQL INNER JOIN
      • SQL LEFT JOIN
      • SQL RIGHT JOIN
      • SQL FULL OUTER JOIN
      • SQL CROSS JOIN
    • SQL Server Cross Apply and Outer Apply
    • SQL Correlated Subquery
    • SQL Subquery
    • SQL Over Clause
    • SQL Server ISNUMERIC Function
    • SQL SERVER @@IDENTITY
    • SQL SERVER ISNULL
    • SQL Server NEWSEQUENTIALID()
    • SQL Server NEWID()
    • SQL Server @@ERROR
    • SQL Server DENSE_RANK()
    • SQL RANK()
    • SQL Server ROW_NUMBER()
    • SQL SERVER TRY_CAST()
    • SQL PARSE Function
    • SQL Server IIF Function
    • SQL Server CHOOSE Function
    • SQL DATENAME()
    • SQL DATEFROMPARTS
    • SQL DATEPART()
    • SQL DATEDIFF()
    • SQL DATEADD()
    • Date and Time Conversions in SQL Server
    • SQL GETDATE()
    • SQL CURRENT_TIMESTAMP
    • SQL CHARINDEX()
    • SQL Server PATINDEX
    • SQL SERVER REVERSE
    • SQL Server STRING_SPLIT Function
    • SQL Server STRING_AGG Function
    • SQL REPLICATE()
    • SQL SUBSTRING()
    • SQL LEN()
    • SQL STUFF
    • SQL Server CONCAT_WS Function
    • SQL Server CHOOSE Function
    • SQL TRIM()
    • SQL Server RTRIM()
    • SQL Server LTRIM()
    • SQL Server Format Function
    • SQL TRANSLATE()
    • SQL LOWER()
    • SQL UPPER()
    • SQL SPACE
    • SQL COALESCE()
    • SQL SERVER REPLACE
    • SQL QUOTENAME Function
    • SQL Server Lead Function
    • SQL Server Lag Function
    • SQL Server FIRST_VALUE Function
    • SQL Server LAST_VALUE Function
    • SQL Server MAX
    • SQL Server MIN
    • SQL Server AVG
    • SQL Server SUM
    • SQL COUNT
    • SQL Server CEILING Function
    • SQL Server FLOOR Function
    • SQL Random Number
    • SQL ROUND Function
  • SQL Advance Concepts
  • SQL Server Tricks & Tips
  • SQL Interview Q & A
  • SSRS-SQL Server Reporting Services

Page Views

  • 710,010 hits

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Translate this website..

Search for Tutorial

Generic selectors
Exact matches only
Search in title
Search in content
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy
  • Home
  • SQL Basics Concepts
  • SQL Advance Concepts
  • SQL Interview Q & A
  • SQL Server Tricks & Tips
  • SSRS-SQL Server Reporting Services
  • SSIS-SQL Server Integration Services
  • POWER BI
  • Python with Machine Learning
  • Microsoft Azure
  • HTML Color picker

SqlSkull. All Rights Reserved

 

Loading Comments...