What is the usage of like in oracle

In Oracle, the like keyword can be used with the where clause to achieve fuzzy query effects. It can be used with wildcards to limit expression conditions. The syntax is "SELECT*FROM user WHERE uname LIKE wildcard" .

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

What is the usage of like in oracle

1. We can use the like keyword in the where clause to achieve the effect of Oracle fuzzy query; in the where clause, we can use datetime, Columns of char and varchar field types use the Like keyword with wildcards to implement fuzzy queries. The following are the wildcards that can be used:

(1) %: zero or more characters. There are three situations when using %

Field like Records where the '%keyword%' field contains "keyword"

Field like Records where the 'keyword%' field starts with "keyword"

Field like ' %Records whose keyword' field ends with "keyword"

Example:

SELECT * FROM [user] WHERE uname LIKE ‘%三%’

Search results: "Zhang San", "Xiao San", "Three-legged Cat", "Cat Three-legged" Yes Find all the records of "three".

SELECT * FROM [user] WHERE uname LIKE ‘%三’ （从后开始匹配）

Search results: "Zhang San", "Xiao San"

In addition, if you need to find records with both "三" and "cat" in uname, please use the and condition

SELECT *FROM [user] WHERE uname LIKE ‘%三%’ AND uname LIKE ‘%猫%’

If you use SELECT * FROM [user] WHERE uname LIKE '%三%cat%', although you can search for "cat with three legs", you cannot search for "cat with three legs".

(2)_: Any single character (underscore) is often used to limit the character length of expressions:

Example:

SELECT * FROM [user] WHERE uname LIKE ‘三’

Search results: "cat three legs" like this uname is three characters and the middle one is "three";

SELECT * FROM [user] WHERE uname LIKE ‘三__’;

Search results: "Three-legged cat" so uname is three characters and the first one is "three";

(3) []: Characters within a certain range, representing one of the characters listed in brackets (similar to a regular expression). Specify a character, string, or range, requiring the matched object to be any one of them.

Example:

SELECT * FROM [user] WHERE u_name LIKE ‘[张李王]三’

Search results: "Zhang San", "Li San", "Wang San" (instead of "Zhang Li Wang San");

Such as [ ] contains a series of characters (01234, abcde, etc.), which can be abbreviated as "0-4", "a-e"

SELECT * FROM [user] WHERE u_name LIKE ‘老[1-9]’

Search results: "Old 1", "Old 2",... , "老9";

(4) [^]: Characters that are not within a certain range are used in the opposite way to [ ].

2. The instr(strSource, strTarget) function is provided in Oracle, which is much more efficient than using the '% keyword%' mode.

The instr function also has three situations:

instr(field,'keyword')>0 is equivalent to field like '%keyword%'

instr(field ,'keyword')=1 is equivalent to field like 'keyword%'

instr(field,'keyword')=0 is equivalent to field not like '%keyword%'

Example:

SELECT * FROM [user] WHEREinstr(uname ,’三’)>0

For usage, please refer to Like above.

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;

Recommended tutorial:《OracleVideoTutorial》

The above is the detailed content of What is the usage of like in oracle. For more information, please follow other related articles on the PHP Chinese website!