SQL statement Fuzzy query

SQL statement to implement fuzzy query, some things are always unclear, now make a note.


We can use like in the WHERE clause to achieve the effect of the fuzzy query, in the WHERE clause, you can use the LIKE clause with wildcard characters for the datetime, char, and varchar field types to select those data records that are "very similar ...", the following are the wildcard characters that can be used:
% 0 or more characters
_ Single any character (underscore)
\ special Characters
[] A character within a range, such as [0-9] or [aeth]
[^] characters that are not within a range, such as [^0-9] or [^aeth]
The latter two require Oracle 10g to use a regular regexp_like that supports like.

In terms of conditions, the SQL statement in Oralce provides four matching patterns:

1,%: represents any 0 or more characters. Can match any type and length of the character, in some cases, if Chinese, please use two percent sign (%).
such as SELECT * from [user] WHERE the u_name like '% of three '
will be u_name as "Zhang San", "Zhang Cat Three", "three-legged Cat", "Tang Sanzang" and so on Have "three" records all find out.
Also, if you need to find records in U_name that have both "three" and "cat", use the AND condition
Select * FROM [user] WHERE u_name like '% three ' and u_name like '% cat% '
  ;
If you use SELECT * from [user] WHERE u_name like '% cat% '
Although you can search for "three-legged cat", but can not search out the eligible "Zhang Cat three."
 
2,_: Represents any single character. Matches a single arbitrary character, which is commonly used to restrict the character-length statement of an expression:
such as SELECT * from [user] WHERE u_name like ' _ three _ '
only to find "Tang Sanzang" such u_name as three words and the middle one word is "three";
Another example is SELECT * from [user] WHERE u_name like ' three __ ';
only find "three-legged cat" this name is three words and the first word is "three";

3,[]: represents one of the characters listed in parentheses (similar to a regular expression). Specifies a character, string, or range that requires matching objects to be any of them.
such as SELECT * from [user] WHERE u_name like ' [Zhang Li Wang] Three '
will find "Zhang San", "Lie Triple", "Wang San" (not "Zhangli Kang"),
such as [] a series of characters (01234, ABCDE, etc.) Can be slightly written as "0-4", "A-E"
Select * from [user] WHERE u_name like ' old [1-9] '
will find "Old 1", "Old 2" 、......、 "Old 9";
 
4,[^] : Represents a single character that is not listed within the parentheses. The value is the same as [], but it requires that the matched object be any character other than the specified character.
such as SQL Server:select * from [user] WHERE u_name like ' [^ Zhang Li Wang] Three '     will find the surname "Zhang", "Li", "King" of the "Zhao three", "Magozo" and so on;
Oracle 10g or more uses:
 
Select*fromtablewhereregexp_like (name, ' [Zhang Li Wang] three ');
SELECT * FROM [user] WHERE u_name like ' old [^1-4] '; The
will exclude "old 1" to "Old 4", Looking for "old 5", "Old 6" 、......
Note: Oracle like does not support regular, you can use regular regexp_like
 5 that supports like, and when a query contains a wildcard character
because of the wildcard character, we query for special characters "%", "_", "[" Statements are not implemented properly, and the special characters are queried normally by "[]". Thus we write the following function:
 
function Sqlencode (str)
Str=replace (str, "[", "[[]") ' This sentence must be at the top
Str=replace (str, "_", "[_]")
Str=replace (str, "%", "[%]")
Sqlencode=str
End Function
 
The unknown origin string is processed by the function before the query.

SQL statement Fuzzy query