How to deal with underscores when Oracle uses like query

Time:2022-5-8

For example: query ZJ_ STANDARD_ The data in the item table ‘(‘ 1 ‘) contains

--The execution of the following query statements will find that the data queried is not the data we want
select * from ZJ_STANDARD_ITEM t where t.name1 like '%_(%';

Cause analysis

Underline “” In Oracle, it doesn’t simply mean underline, but means to match any single character! For example, the above query statement means that any character on the left of “(” will be queried

[solution]

Use escape() function

Escape keyword is often used to make some special characters, such as wildcard: ‘%’, ‘_‘ Escape is the meaning of their original characters. The defined escape characters usually use ”, but other symbols can also be used.

For example, the above conditional statement can be written as where t.name1 like ‘% 1’_ (%’escape ‘;), this “\” can be changed to other characters

--Use escape function to set "" Meaning of underline
select t.name1 from ZJ_STANDARD_ITEM t where t.name1 like '%\_(%'escape '\';

Knowledge point expansion: the use of like fuzzy query in Oracle

Like fuzzy query
placeholder
%Replace one or more characters
_ Replace only one character

Any single character in the [charlist] character column

[^ charlist] or [! Charlist] any single character that is not in the character column

(1) Query employee information with user name starting with’s’


  Select * from emp where ename like 'S%'

(2) Query employee information whose user name ends with’s’


 Select * from emp where ename like '%S'

(3) Query the employee information whose second letter of user name is’ a ‘


  select * from emp where ename like '_A%'

(4) Query the employee information whose user name is’ a ‘in the third letter


  select * from emp where ename like '__A%'

(5) Query the employee information with ‘a’ in the user name


  select * from emp where ename like '%A%'

(6) Query the employee information that does not contain ‘a’ in the user name


  select * from emp where ename not like '%A%'

(7) Select a person whose city of residence starts with “a” or “L” or “n” from the “persons” table:


 SELECT * FROM Persons WHERE City LIKE '[ALN]%'

(8) Select a person from the “persons” table who lives in a city that does not start with “a” or “L” or “n”:


SELECT * FROM Persons WHERE City LIKE '[!ALN]%'

This is the end of this article on the processing of underscores when Oracle uses like query. For more information about the processing of underscores in Oracle like query, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!