SQL like usage fuzzy query and SQL fuzzy query

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