The method of Oracle special character fuzzy query

Recently, when writing the DAO layer, I encountered a problem, that is, when using like for fuzzy query, input underline, can not accurately find the data, but return all the data.

This makes me very curious, Baidu only then found that the original is because some special characters need to be escaped in order to query.

First understand what special characters are.

In the ASCII code table, the range of special characters is as follows. That is, from 32~47 58~64 91~96 123~126.

There are two ways to convert a fuzzy query.

1. Using the Regexp_like method, this is a method queried using a regular expression, so there is a character that needs to be escaped to query, such as $ *, the escape character is a backslash \, so the backslash is also going to go. This only discusses the case where a string is contained in a character, and the match for regular expressions is not discussed.

2. Use the like keyword to escape with the escape keyword.

The character matching operation can use wildcards "%" and "_":%: denotes any character, including 0; _: denotes an arbitrary character; the input underline above will find out all because any one character can be queried. Also, for single quotes, in Oracle, such as SELECT * from Tabletest t where t.name like '% '% ' escape ' \ ' then the ' in the like statement will match the previous single quote, resulting in a syntax error. Using escape symbols also does not end properly. You need to turn a single single quote into two single quotes, select * from Tabletest t where t.name like '% '% ' escape ' \ ' So the above two characters, and the characters used to escape (usually using backslashes) need to be escaped.
So again, in Java code, if you use JDBC's preparedstatement for precompilation, is it not necessary to escape? Just do it! The answer is no, or it needs to be escaped. Note that the following is not escaped, and the results are still all records. After escaping, it is accurate. Also note two points: 1. JDBC automatically adds single quotes, and you do not have to add single quotes 2 when assigning values. JDBC automatically converts a single single quotation mark to two, and does not have to write two single quotes when assigning a value

The method of Oracle special character fuzzy query