SQL Fuzzy Query

When executing a database query, there is a complete query and a fuzzy query.

The general Fuzzy statement format is as follows:

SELECT field from table WHERE a field like condition;

With regard to conditions, SQL provides four matching modes:

1,%: denotes 0 or more characters.

Can match any type and any length of characters, in some cases, if Chinese, please use two percent sign (%).

For example: SELECT * from Flow_user where username like '% Wang ';

will be flow_user This table inside, column name username and "King" records all query out.

In addition, if you need to find flow_user this table, the field username both "Tang" and "English" records, you can use and conditions,

SELECT * FROM Flow_user where username like '% UK ' and username like '% don ';

You can query out all the content that contains "English" and "Tang", and the position of "English" and "tang" is irrelevant.

If you use SELECT * from Flow_user where username like '% English% ';

Can be found to contain the "English Tang" content, but do not find out "tang" content.

2, _: Represents any single character. Matches a single arbitrary character, which is commonly used to limit the character length of an expression:

SELECT * FROM Flow_user where username like ' _ english _ ';

can only find "Wang Yingkun" so username for three words and the middle one word is "English" content.

Another example: SELECT * from Flow_user where username like ' English __ ';

can only find "hero point" so username for three characters and the first word is "English" content.

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 one of them.

SELECT * FROM Flow_user where username like ' [Wang Lizhang] Fly ';

Will find "Wang Fei" "Li Fei" "Zhang Fei" (not "Zhang Wang Lifei").

If [] There are a series of characters (01234,ABCDE and the like) can be slightly written as "0-4", "A-E":

SELECT * FROM Flow_user where username like ' old [0-9] ';

Will find "Old 1", "Old 2" 、......、 "Old 9";

Oracle 10g and above version usage is:

SELECT * from Flow_user where regexp_like (username, ' [Zhang Wangli] fly ');

4, [^]: Represents a single character that is not listed in parentheses. It takes the same as [], but it requires that the matching object be any character other than the specified character.

SELECT * FROM Flow_user where username like ' [^ Wang Lizhang] Fly ';

Will find not "Wang Fei" "Li Fei" "Zhang Fei" "Zhao Fei", "Wu Fei" and so on.

Note: Oracle like does not support regular, you can use regular regexp_like that support like

5. When the query contains a wildcard:

Because of the wildcard character, which causes the query special characters "%", "_", "[" the statement can not be implemented normally, the special characters with "[]" in the normal query can be.

function Sqlencode (str) str=replace (str, "[", "[[]") ' This sentence must be in the first str=replace (str, "_", "[_]") Str=replace (str, "%", "[%]") Sqlencode=str End Function

SQL Fuzzy Query