Oracle SQL like fuzzy query-Wildcard

 

In the Where clause, you can use the Like clause with wildcards to select columns of the datetime, char, and varchar field types... "Data records. The following are available wildcard characters:

% Zero or multiple characters

_ Any single character (underline)

\ Special characters

[] Characters in a certain range, such as [0-9] or [aeth]

[^] Characters out of a certain range, such as [^ 0-9] or [^ aeth]

SQL provides four matching modes for conditions:

 

 

1. %: represents any 0 or multiple characters. It can match any type and length of characters. In some cases, if it is Chinese, use two percent signs (%.

For example, SELECT * FROM [user] WHERE u_name LIKE '% 3%'

All records of "three", such as "three cats", "three cats", and "three cats", will be found.

In addition, if you need to find records with "three" and "cat" in u_name, use the and condition.

SELECT * FROM [user] WHERE u_name LIKE '% 3%' AND u_name LIKE '% cat %'

If SELECT * FROM [user] WHERE u_name LIKE '% 3% cat %' is used'

Although you can search for "three-legged cats", you cannot search for "three-legged cats" that meet the search criteria ".

 

 

2. _: represents any single character. Matches any character. It is often used to limit the character length of an expression:

For example, SELECT * FROM [user] WHERE u_name LIKE '_ 3 _'

Only find out that "Tang sanzang" has three u_name characters and the middle word is "3;

For example, SELECT * FROM [user] WHERE u_name LIKE 'three __';

Find out that the name of "Three-Legged cats" is three words and the first word is "three;

 

 

3. []: represents one of the characters listed in brackets (similar to regular expressions ). Specifies a character, string, or range. The matching object must be one of them.

For example, SELECT * FROM [user] WHERE u_name LIKE '[Zhang Li Wang] 3'

We will find "Zhang San", "Li San", and "Wang San" (instead of "Zhang Li Wang San ");

For example, if [] contains a series of characters (01234 or abcde), it can be slightly written as "0-4" or "a-e"

SELECT * FROM [user] WHERE u_name LIKE 'Old [1-9]'

Will find out "Old 1", "old 2 ",...... , "Old 9 ";

 

 

4. [^]: represents a single character not listed in parentheses. The value is the same as [], but it requires that the matched object be any character other than the specified character.

For example, SELECT * FROM [user] WHERE u_name LIKE '[^ Zhang Li Wang] 3'

We will find "Zhao San" and "Sun San" without the surname "Zhang", "Li", and "Wang;

SELECT * FROM [user] WHERE u_name LIKE 'Old [^ 1-4] ';

Will exclude "Old 1" to "old 4", looking for "old 5", "old 6 ",......