Latest web development tutorials

SQLite Like clause

SQLiteLIKE operator is used to match a text value specified wildcard pattern.If the search expression and expression pattern matching, LIKE operator returns true (true), which is 1. There are two wildcards used with LIKE operator:

  • Percent (%)

  • An underscore (_)

Percent sign (%) represents zero, one or more digits or characters. An underscore (_) represents a single digit or character. These symbols can be used in combination.

grammar

% And _ The basic syntax is as follows: 

SELECT FROM table_name
WHERE column LIKE 'XXXX%'

or 

SELECT FROM table_name
WHERE column LIKE '% XXXX%'

or

SELECT FROM table_name
WHERE column LIKE 'XXXX_'

or

SELECT FROM table_name
WHERE column LIKE '_XXXX'

or

SELECT FROM table_name
WHERE column LIKE '_XXXX_'

You can use the AND or OR operator to combine the N number of conditions. Here, XXXX can be any number or string value.

Examples

The following examples demonstrate the '%' and '_' operator different places with the LIKE clause:

语句描述
WHERE SALARY LIKE '200%'查找以 200 开头的任意值
WHERE SALARY LIKE '%200%'查找任意位置包含 200 的任意值
WHERE SALARY LIKE '_00%'查找第二位和第三位为 00 的任意值
WHERE SALARY LIKE '2_%_%'查找以 2 开头，且长度至少为 3 个字符的任意值
WHERE SALARY LIKE '%2'查找以 2 结尾的任意值
WHERE SALARY LIKE '_2%3'查找第二位为 2，且以 3 结尾的任意值
WHERE SALARY LIKE '2___3'查找长度为 5 位数，且以 2 开头以 3 结尾的任意值

Let's take a practical example, suppose COMPANY table has the following records: 

ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0

Here is an example, it shows the COMPANY table AGE 2 all records that begin with: 

sqlite> SELECT * FROM COMPANY WHERE AGE LIKE '2%';

This produces the following results: 

ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0

Here is an example, it displays the text COMPANY ADDRESS table contains a hyphen (-) all records: 

sqlite> SELECT * FROM COMPANY WHERE ADDRESS LIKE '% -%';

This produces the following results: 

ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
4 Mark 25 Rich-Mond 65000.0
6 Kim 22 South-Hall 45000.0