Skip to content
Online Education & Certification

Online Education & Certification

  • Basics of Computer
  • Microsoft Windows
  • Microsoft Office
  • Tally
  • Java
  • SQL
  • Hardware
  • Networking
  • MySQL
  • Python
  • Digital Marketing
  • C Language
  • C ++
  • Visual Basic
  • Html 5 & CSS
Online Education & Certification

SQL LIKE Operator

November 10, 2017 selva SQL

SQL LIKE Operator : SQL LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards used in conjunction with the LIKE operator:

  • % – The percent sign represents zero, one, or multiple characters
  • _ – The underscore represents a single character

Note:

  • MS Access uses a question mark (?) instead of the underscore (_).
  • The percent sign and the underscore can also be used in combinations!

LIKE Syntax

SELECT column1, column2, …
FROM table_name
WHERE columnN LIKE pattern;

Tip:

  • You can also combine any number of conditions using AND or OR operators.
  • Here are some examples showing different LIKE operators with ‘%’ and ‘_’ wildcards:
LIKE Operator Description
WHERE CustomerName LIKE ‘a%’ Finds any values that starts with “a”
WHERE CustomerName LIKE ‘%a’ Finds any values that ends with “a”
WHERE CustomerName LIKE ‘%or%’ Finds any values that have “or” in any position
WHERE CustomerName LIKE ‘_r%’ Finds any values that have “r” in the second position
WHERE CustomerName LIKE ‘a_%_%’ Finds any values that starts with “a” and are at least 3 characters in length
WHERE ContactName LIKE ‘a%o’ Finds any values that starts with “a” and ends with “o”

 

Demo Database

Below is a selection from the “Customers” table in the Northwind sample database:

CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden

 

SQL LIKE Examples

The following SQL statement selects all customers with a CustomerName starting with “a”:

Example

SELECT * FROM Customers
WHERE CustomerName LIKE ‘a%’;

output

The following SQL statement selects all customers with a CustomerName ending with “a”:

Number of Records: 4

CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK

Example

SELECT * FROM Customers
WHERE CustomerName LIKE ‘%a’;

 

The following SQL statement selects all customers with a CustomerName that have “or” in any position:

Example

SELECT * FROM Customers
WHERE CustomerName LIKE ‘%or%’;

 

Output

Number of Records: 11

CustomerID CustomerName ContactName Address City PostalCode Country
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
36 Hungry Coyote Import Store Yoshi Latimer City Center Plaza 516 Main St. Elgin 97827 USA
40 La corne d’abondance Daniel Tonini 67, avenue de l’Europe Versailles 78000 France
43 Lazy K Kountry Store John Steel 12 Orchestra Terrace Walla Walla 99362 USA
52 Morgenstern Gesundkost Alexander Feuer Heerstr. 22 Leipzig 04179 Germany
53 North/South Simon Crowther South House 300 Queensbridge London SW7 1RZ UK
55 Old World Delicatessen Rene Phillips 2743 Bering St. Anchorage 99508 USA
72 Seven Seas Imports Hari Kumar 90 Wadhurst Rd. London OX15 4NB UK
80 Tortuga Restaurante Miguel Angel Paolino Avda. Azteca 123 México D.F. 05033 Mexico
88 Wellington Importadora Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil

 

The following SQL statement selects all customers with a CustomerName that have “r” in the second position:

Example

SELECT * FROM Customers
WHERE CustomerName LIKE ‘_r%’;

 

Output

Number of Records: 11

CustomerID CustomerName ContactName Address City PostalCode Country
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
17 Drachenblut Delikatessend Sven Ottlieb Walserweg 21 Aachen 52066 Germany
20 Ernst Handel Roland Mendel Kirchgasse 6 Graz 8010 Austria
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany
26 France restauration Carine Schmitt 54, rue Royale Nantes 44000 France
27 Franchi S.p.A. Paolo Accorti Via Monte Bianco 34 Torino 10100 Italy
32 Great Lakes Food Market Howard Snyder 2732 Baker Blvd. Eugene 97403 USA
33 GROSELLA-Restaurante Manuel Pereira 5ª Ave. Los Palos Grandes Caracas 1081 Venezuela
60 Princesa Isabel Vinhoss Isabel de Castro Estrada da saúde n. 58 Lisboa 1756 Portugal
81 Tradição Hipermercados Anabela Domingues Av. Inês de Castro, 414 São Paulo 05634-030 Brazil
82 Trail’s Head Gourmet Provisioners Helvetius Nagy 722 DaVinci Blvd. Kirkland 98034 USA

 

The following SQL statement selects all customers with a CustomerName that starts with “a” and are at least 3 characters in length:

Example

SELECT * FROM Customers
WHERE CustomerName LIKE ‘a_%_%’;

Output

Number of Records: 4

CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK

 

The following SQL statement selects all customers with a ContactName that starts with “a” and ends with “o”:

Example

SELECT * FROM Customers
WHERE ContactName LIKE ‘a%o’;

Output

Number of Records: 3

CustomerID CustomerName ContactName Address City PostalCode Country
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
69 Romero y tomillo Alejandra Camino Gran Vía, 1 Madrid 28001 Spain

 

The following SQL statement selects all customers with a CustomerName that does NOT start with “a”:

Example

SELECT * FROM Customers
WHERE CustomerName NOT LIKE ‘a%’;

 

output

Number of Records: 12

CustomerID CustomerName ContactName Address City PostalCode Country
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
6 Blauer See Delikatessen Hanna Moos Forsterstr. 57 Mannheim 68306 Germany
7 Blondel père et fils Frédérique Citeaux 24, place Kléber Strasbourg 67000 France
8 Bólido Comidas preparadas Martín Sommer C/ Araquil, 67 Madrid 28023 Spain
9 Bon app’ Laurence Lebihans 12, rue des Bouchers Marseille 13008 France
10 Bottom-Dollar Marketse Elizabeth Lincoln 23 Tsawassen Blvd. Tsawassen T2F 8M4 Canada
11 B’s Beverages Victoria Ashworth Fauntleroy Circus London EC2 5NT UK
12 Cactus Comidas para llevar Patricio Simpson Cerrito 333 Buenos Aires 1010 Argentina
13 Centro comercial Moctezuma Francisco Chang Sierras de Granada 9993 México D.F. 05022 Mexico
14 Chop-suey Chinese Yang Wang Hauptstr. 29 Bern 3012 Switzerland
15 Comércio Mineiro Pedro Afonso Av. dos Lusíadas, 23 São Paulo 05432-043 Brazil
16 Consolidated Holdings Elizabeth Brown Berkeley Gardens 12 Brewery London WX1 6LT UK

 

sql likeSQL LIKE Operator

Post navigation

Previous Post:SQL Wildcards
Next Post:SQL COUNT(), AVG() and SUM()Functions

SQL

  • Introduction to SQL
  • SQL Syntax
  • SQL Statements
  • SQL SELECT Statement
  • SQL SELECT DISTINCT Statement
  • SQL WHERE Clause
  • SQL AND, OR and NOT Operators
  • SQL ORDER BY Keyword
  • SQL INSERT INTO Statement
  • SQL NULL Values
  • SQL UPDATE Statement
  • SQL DELETE Statement
  • SQL TOP, LIMIT or ROWNUM Clause
  • SQL MIN() and MAX() Functions
  • SQL COUNT(), AVG() and SUM()Functions
  • SQL LIKE Operator
  • SQL Wildcards
  • SQL IN Operator
  • SQL BETWEEN Operator
  • SQL Aliases
  • SQL Self JOIN
  • SQL UNION Operator
  • SQL GROUP BY Statement
  • SQL HAVING Clause
  • SQL EXISTS Operator
  • SQL ANY and ALL Operators
  • SQL SELECT INTO Statement
  • SQL INSERT INTO Statement
  • SQL NULL Functions
  • SQL Comments
  • SQL CREATE DATABASE Statement
  • SQL DROP DATABASE Statement
  • SQL CREATE TABLE Statement
  • SQL DROP TABLE Statement
  • SQL ALTER TABLE Statement
  • SQL Constraints
  • SQL NOT NULL Constraint
  • SQL UNIQUE Constraint
  • SQL PRIMARY KEY Constraint
  • SQL FOREIGN KEY Constraint
  • SQL CHECK Constraint
  • SQL DEFAULT Constraint
  • SQL CREATE INDEX Statement
  • SQL AUTO INCREMENT Field
  • SQL Working With Dates
  • SQL Data Types MySQL, SQL Server, MS Access
  • SQL Views
  • SQL Injection
  • SQL Hosting
  • MySQL Functions
  • SQL Server Functions
  • MS Access Functions
  • Oracle Functions
  • SQL Operators

Answer these questions

open all | close all
C Language
Chapter 1: Introduction to ‘C’ and ‘C’ Fundamentals
Chapter 2: Data Input/Output and ‘C’ Operators
Chapter 3: Control Statement
Chapter 4: Arrays
Chapter 5: Functions
Chapter 6: Pointers
Chapter 7: Structures
Chapter 8: More On Pointers
Chapter 9: File Handling
Chapter 10: Introduction to Data Structure
Chapter 11: Linear List
Chapter 12: Introduction to Graphics
C++
Chapter 1: Introduction to OOPS
Chapter 2: Introduction to C++
Chapter 3: Programming Constructs
Chapter 4: Arrays & Pointers
Chapter 5: Functions
Chapter 6: Class & Objects
Chapter 7: Friend Function
Chapter 8: Constructors & Destructors
Chapter 9: Operator Overloading
Chapter 10: Inheritance
Chapter 11: Virtual Functions & Polymorphism
Chapter 12: Files
Chapter 13: Templates
Chapter 14: Introduction to Graphics
MS Excel
Chapter 1: Introduction to MS Excel
Chapter 2: Working With Excel Sheet
Chapter 3: Advanced Formula and Functions
Chapter 4: Creating Charts in Excel
Chapter 5: Analyzing Data With Excel
MS PowerPoint
PowerPoint
MS Word
Chapter 1: Introduction to Word 2007
Chapter 2: Creating a New Document
Chapter 3: Insert Tab
Chapter 4: Page Layout Tab
Chapter 5: Text Basics
Chapter 6: Text Formatting
Chapter 7: Creating Tables
Chapter 8: Formatting a Table
Chapter 9: References Tab
Chapter 10: Mailings Tab
Chapter 11: Review Tab
Chapter 12: View Tab
Python
Chapter 1: Introduction To Python
Chapter 2: Variable Declarations
Chapter 3: Condition Execution
Chapter 4: Python Functions
Chapter 5: Python Modules
Chapter 6: Sequences
Chapter 7: Python Strings
Chapter 8: Python File I/O
Chapter 9: Python Errors and Built-In Exceptions
Chapter 10: Python Namespace And Scope
Chapter 11: Python Objects and Class
Chapter 12: Python Inheritance
Chapter 13: Python – Network Programming
Chapter 14: Multithreaded Programming
Chapter 15: Python MySql Database Access
Chapter 16: Python – GUI Programming[TKinter]
Chapter 17: Image Processing In Python With Pillow
Tally Prime QA
Chapter 2: Getting Started
Chapter 3: Company Info & Features
Chapter 4: Accounts Info
Chapter 5: Inventory Info
Chapter 6: Voucher Entry
Chapter 7: Display & Configuration
Chapter 8: Printing
Chapter 9: Banking
Chapter 10: Voucher Type & Class
Chapter 11: Bill Wise Details
Chapter 12: Cost Centre & Category
Chapter 13: Job Costing
Chapter 14: Multi-Currency
Chapter 15: Interest Calculation
Chapter 16: Budget & Credit Limit
Chapter 17: Scenario Management
Chapter 18: Order Processing & Tracking System
Chapter 19: Reorder Level & Status
Chapter 20: Batch wise Details
Chapter 21: Bill Of Material (BoM)
Chapter 22: Price Level & List
Chapter 23: Different Actual & Billed Quantity
Chapter 24: Consolidation Of Books
Chapter 25: Split Company Data
Chapter 26: Backup & Restore
Chapter 27: Security Control & Tally Audit
Chapter 28: Tally Vault
Chapter 29: Export & Import
Chapter 30: Open Database Connectivity(ODBC)
Chapter 31: Concurrent Multilingual Support
Chapter 32: Point of Sales(POS)
Chapter 33: Tally.Net
Chapter 34: Payroll
Chapter 35: Goods and Service Tax(GST)
Chapter 36: Tax Deducted at Source
Chapter 37: Tax Collected at Sources
Visual Basic
Chapter 9: Files & Graphics
Chapter 10: Database, DAO & RDO
Chapter 11: ADO
Chapter 12: Objects
Chapter 13: OLE Object Linking And Embedding
Chapter 14: ActiveX
Chapter 15: Internet Applications & DHTML
Windows & Internet
Chapter 1: Introduction to Computer Part A
Chapter 1: Introduction to Computer Part B
Chapter 2: Computer Virus
Chapter 3: Introduction to Windows
Chapter 4: Accessing Information
Chapter 5: Managing Files & Folders
Chapter 6: Customising
Chapter 7: Accessories
Chapter 8: Introduction to the Internet
Chapter 9: World Wide Web
Chapter 10: Multimedia on the Web
Chapter 11: Search the Web
Chapter 12: Electronic Mail
Chapter 13: Web Glossary

Online Exams

  • ASP & XML
  • Autocad
  • C Language
  • C++
  • DTP
  • Hardware
  • HTML
  • Java
  • MS Excel
  • MS Word
  • MySql
  • Python
  • Visual Basic
  • Tally Prime
WordPress Theme: Gambit by ThemeZee.