SQL like usage fuzzy query and SQL fuzzy query
Fuzzy query:
References: http://www.w3school.com.cn/ SQL/SQL _wildcards.asp
When searching for data in a database, you can use the SQL wildcard.
SQL wildcard Like
When searching for data in a database, the SQL wildcard can replace one or more characters.
The SQL wildcard must be used with the LIKE operator.
In SQL, you can use the following wildcard characters:
Wildcard |
Description |
% |
Replace one or more characters |
_ |
Replace only one character |
[Charlist] |
Any single character in the character Column |
[^ Charlist] Or [! Charlist] |
Any single character not in the character Column |
Original table (used in the example ):
Persons table:
Id |
LastName |
FirstName |
Address |
City |
1 |
Adams |
John |
Oxford Street |
London |
2 |
Bush |
George |
Th Avenue |
New York |
3 |
Carter |
Thomas |
Changan Street |
Beijing |
Example 1 with the % wildcard
Now, we want to select the Persons who live in the city starting with "Ne" from the "Persons" table above:
We can use the following SELECT statement:
SELECT * FROM PersonsWHERE City LIKE 'Ne%'
Result set:
Id |
LastName |
FirstName |
Address |
City |
2 |
Bush |
George |
Th Avenue |
New York |
Example 2
Next, we want to select people from the "Persons" table who live in cities containing "lond:
We can use the following SELECT statement:
SELECT * FROM PersonsWHERE City LIKE '%lond%'
Result set:
Id |
LastName |
FirstName |
Address |
City |
1 |
Adams |
John |
Oxford Street |
London |
Example 1
Now, we want to select the "eorge" person after the first character of the name from the "Persons" table:
We can use the following SELECT statement:
SELECT * FROM PersonsWHERE FirstName LIKE '_eorge'
Result set:
Id |
LastName |
FirstName |
Address |
City |
2 |
Bush |
George |
Th Avenue |
New York |
Example 2
Next, we want to start the last name of the record selected from the "Persons" table with "C", followed by any character, followed by "r", followed by any character, then "er ":
We can 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 |
Example 1 using the [charlist] wildcard
Now, we want to select people from the preceding "Persons" table who begin with "A", "L", or "N:
We can 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 |
Th Avenue |
New York |
Example 2
Now, we want to select the city of residence from the "Persons" table above.Do not usePersons starting with "A" or "L" or "N:
We can use the following SELECT statement:
SELECT * FROM PersonsWHERE City LIKE '[!ALN]%'
Result set:
Id |
LastName |
FirstName |
Address |
City |
3 |
Carter |
Thomas |
Changan Street |
Beijing |