SQLite LIKE Clause (Operator)

The SQLite LIKE operator is used to match text values against a pattern using wildcards. In the case search expression is matched to the pattern expression, the LIKE operator will return true, which is 1.

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

  • The percent sign (%)
  • The underscore (_)

The percent sign represents zero, one, or multiple numbers or characters. The underscore represents a single number or character.

Syntax:

snippet
SELECT FROM table_name
WHERE column LIKE 'XXXX%'

or

snippet
SELECT FROM table_name
WHERE column LIKE '%XXXX%'

or

snippet
SELECT FROM table_name
WHERE column LIKE 'XXXX_'

or

snippet
SELECT FROM table_name
WHERE column LIKE '_XXXX'

or

snippet
SELECT FROM table_name
WHERE column LIKE '_XXXX_'

Here, XXXX could be any numeric or string value.

Example:

We have a table named 'STUDENT' having following data:

Sqlite Like clause 1

In these examples the WHERE statement having different LIKE clause with '%' and '_' operators and operation is done on 'FEES':

Statement Description
Where FEES like '200%' It will find any values that start with 200.
Where FEES like '%200%' It will find any values that have 200 in any position.
Where FEES like '_00%' It will find any values that have 00 in the second and third positions.
Where FEES like '2_%_%' It will find any values that start with 2 and are at least 3 characters in length.
Where FEES like '%2' It will find any values that end with 2
Where FEES like '_2%3' It will find any values that have a 2 in the second position and end with a 3
Where FEES like '2___3' It will find any values in a five-digit number that start with 2 and end with 3

Example1: Select all records from STUDENT table WHERE age start with 2.

snippet
SELECT * FROM STUDENT WHERE AGE  LIKE '2%';

Output:

Sqlite Like clause 2

Example2:

Select all records from the STUDENT table WHERE ADDRESS will have "a" (a) inside the text:

snippet
SELECT * FROM STUDENT WHERE ADDRESS LIKE '%a%';

Output:

Sqlite Like clause 3
Related Tutorial
Follow Us
https://www.facebook.com/Rookie-Nerd-638990322793530 https://twitter.com/RookieNerdTutor https://plus.google.com/b/117136517396468545840 #
Contents +