The method of Oracle special character fuzzy query

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.