Oracle Fuzzy Query method

In this era of information explosion, how to help users to retrieve the data from the vast amount of data, fuzzy query is essential. So how is fuzzy query implemented in Oracle?

First, we can use the LIKE keyword in the WHERE clause to achieve the effect of the Oracle fuzzy Query, in the WHERE clause, you can use the LIKE keyword with a wildcard character to implement the fuzzy query for the columns of datetime, char, varchar field type The following are the wildcard characters you can use:

(1)%: 0 or more characters, using% of three cases

• Field Like '% Key Words % ' field contains " Key Words " the records  
• Field Like ' Key Words % ' fields to " Key Words " the beginning of the record
• Field Like '% Key Words ' fields to " Key Words " End of record

Example:

• SELECT * FROM [user] WHERE uname like '% three '

Search Result: "Zhang San", "small Three", "three-legged Cat", "cat three Feet" have "three" records all find out.

• SELECT * FROM [user] WHERE uname like '% Three ' (from the beginning to match)

Search Result: "Zhang San", "small Three"

• Also, if you need to find a record of "three" and "cat" in uname, use the and condition

SELECT *from [user] WHERE uname like '% Tri ' and uname like '% cat% '

• If you use SELECT * from [user] WHERE uname like '% cat% ', although can search out "three feet cat", but can not search out "cat three feet."

(2) _: A single character-length statement that is commonly used to restrict expressions:

Example:

• SELECT * FROM [user] WHERE uname like ' _ Three _ '

Search Result: "Cat three Feet" This uname is three characters and the middle one is "three";

• SELECT * FROM [user] WHERE uname like ' three __ ';

Search Result: "Three foot cat" so uname for three characters upper the first one is "three";

(3) []: a character within a range that 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.

Example:

• SELECT * FROM [user] WHERE u_name like ' [Zhang Li Wang] three '

Search Result: "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] '

Search Result: "Old 1", "Old 2" 、......、 "Old 9";

(4) [^]: Characters not within a range, usage is the opposite of [].

The InStr (Strsource,strtarget) function is provided in O R Acle, which is much more efficient than using the '% keyword% ' mode.

The InStr function is also available in three different situations:

InStr (field, ' keywords ') >0 equivalent Field Like '% Key Words % '

InStr (field, ' keywords ') =1 equivalent Field Like ' Key Words % '

InStr (field, ' keywords ') =0 equivalent Field Not like '% Key Words % '

Example:

• SELECT * FROM [user] WHERE InStr (uname, ' three ') >0

Use the reference above like to

Special usage:

Select ID, namefrom user where InStr (' 101914, 104703 ', id) > 0;

It is equivalent to

Select ID, namefrom user where id = 101914 or id = 104703;

The above two methods can be used directly when the amount of data is low, but when the amount of data is particularly large, we should consider the problem of efficiency. Although the efficiency of instr than the LIKE keyword method more efficient, but this is only to a certain extent, far from meeting our needs.

Why are keyword queries so inefficient? This is due to the use of these keyword queries, the database system is not indexed to query, but by sequential scanning method to query. Obviously, this technical feature has caused the inefficiency of the LIKE keyword query. Especially in complex queries or large table queries, the user can obviously feel slower speed.

How to solve the problem of efficiency? The answer is also the index.

Reasonable use of index, can greatly improve the query performance of the database.

The rational application of the index is still under study.

Oracle Fuzzy Query method