Fuzzy query:
Reference: http://www.w3school.com.cn/sql/sql_wildcards.asp
You can use SQL wildcard characters when searching for data in a database.
SQL wildcard like
The SQL wildcard can override one or more characters when searching for data in a database.
SQL wildcard characters must be used with the LIKE operator.
In SQL, you can use the following wildcard characters:
wildcard characters |
Descriptive narrative |
% |
Override one or more characters |
_ |
Replaces only one character |
[Charlist] |
Character columns no matter what the single characters |
[^charlist] Or [!charlist] |
No matter what single character is not in the word columns |
The original table (used in the sample):
Persons table:
Id |
LastName |
FirstName |
Address |
| City
1 |
Adams |
John |
Oxford Street |
London |
2 |
Bush |
George |
Fifth Avenue |
New York |
3 |
Carter |
Thomas |
Changan Street |
Beijing |
Use the% wildcard Example 1
Now. We would like to choose from the "Persons" table above for people living in cities that start with "Ne":
We are able to use the following SELECT statement:
SELECT * from Personswhere city like ' ne% '
Result set:
Id |
LastName |
FirstName |
Address |
| City
2 |
Bush |
George |
Fifth Avenue |
New York |
Example 2
Next, we want to select from the "Persons" table the people who live in cities that include "Lond":
We are able to use the following SELECT statement:
SELECT * from Personswhere city like '%lond% '
Result set:
Id |
LastName |
FirstName |
Address |
| City
1 |
Adams |
John |
Oxford Street |
London |
Using the _ Wildcard Example 1
Today, we want to select the "Eorge" person after the first character of the name from the "Persons" table above:
We are able to use the following SELECT statement:
SELECT * from Personswhere FirstName like ' _eorge '
Result set:
Id |
LastName |
FirstName |
Address |
| City
2 |
Bush |
George |
Fifth Avenue |
New York |
Example 2
Next. We want the last name of the record selected from the "Persons" table to start with "C". Then there is a random character, then "R", then a random character. Then there is "ER":
We are able to use the following SELECT statement:
SELECT * from Personswhere LastName like ' c_r_er '
Result set:
Id |
LastName |
FirstName |
Address |
| City
3 |
Carter |
Thomas |
Changan Street |
Beijing |
Using the [charlist] wildcard Example 1
Today, we would like to select from the "Persons" table above the people who live in cities that start with "A" or "L" or "N":
We are able to use the following SELECT statement:
SELECT * from Personswhere city like ' [aln]% '
Result set:
Id |
LastName |
FirstName |
Address |
| City
1 |
Adams |
John |
Oxford Street |
London |
2 |
Bush |
George |
Fifth Avenue |
New York |
Example 2
Today, we want to choose from the "Persons" table above for people who live in cities that do not start with "A" or "L" or "N":
We are able to use the following SELECT statement:
SELECT * from Personswhere city like ' [! aln]% '
Result set:
Id |
LastName |
FirstName |
Address |
| City
3 |
Carter |
Thomas |
Changan Street |
Beijing |
SQL like uses fuzzy queries