oracle--wildcard, escape escape character, fuzzy query statement

Source: Internet
Author: User
Tags sql error

One, wildcard characters

Wildcard Description Example
%: Matches any string that contains 0 or more characters。 The WHERE title like '%computer% ' will find all titles that contain the word computer anywhere in the title.
_ (Underscore): Match any single character。 WHERE au_fname like ' _ean ' will look for all 4-letter names (Dean, Sean, etc.) ending in EAN.
[ ]: Specify a range, ([a-f]) or any single character in the collection ([abcdef]). WHERE au_lname like ' [C-p]arsen ' will find the last name of the author ending with Arsen and starting with any single character between C and P, for example, Carsen, Larsen, Karsen, and so on.
[^]:does not belong to the specified range,([a-f]) or any single character of the collection ([abcdef]). WHERE au_lname like ' de[^l]% ' will look for the last names of all authors that begin with the de and whose letters are not followed by L.

1. Like ' mc% ' will search for all strings (such as McBadden) that begin with the letter Mc.
2. Like '%inger ' will search for all strings ending with the letter inger (such as Ringer, Stringer).
3. Like '%en% ' will search all strings containing the letter en in any location (e.g. Bennet, Green, McBadden).
4. Like ' _heryl ' will search for all six-letter names (such as Cheryl, Sheryl) ending with the letter heryl.
5. Like ' [Ck]ars[eo]n ' will search for the following strings: Carsen, Karsen, Carson, and Karson (such as Carson).
6. Like ' [M-z]inger ' will search for all names (such as Ringer) ending with the string inger, starting with any single letter from M to Z.
7, like ' m[^c]% ' will search starts with the letter M, and the second letter is not all names of C (such as Macfeather).

Second, escape escape character

User input If there are no restrictions, you must transform the special characters.
For example, if you do not transform a single quotation mark, a database error can occur and may even cause the system to crash.
But the avoidance method is very simple, as long as the single quotation mark ['] into two single quotation marks ["] is OK.

Example: SELECT * from TBL WHERE COL = ' ABC ' DEF ';
Although the statement for a fuzzy query does not have a SQL error, but does not evade it, it cannot get the value to retrieve.
Avoidance methods are more complex than single quotes. You need to use an escape character. Convert [%] to [/%], [_] to [/_], and then add [ESCAPE '/'].
Example: SELECT * from the TBL WHERE COL like ' abc/%/_% ' ESCAPE '/';
※ The last% is a wildcard character.

If you do a Japanese project, the full-width characters will appear [%], [_], and the two full-width characters will also be treated as half-width wildcards. So when transforming, you need to transform the full-width [%], [_] at the same time.
Example: SELECT * from the TBL WHERE COL like ' abc/%/_/%/_% ' ESCAPE '/';
The transition seems to be over, but don't forget the escape character itself, in case the user enters an escape character,
The above processing will cause a SQL error. Therefore, the escape character must also be transformed. The transformation method is to convert [/] to [//].
Example: SELECT * from the TBL WHERE COL like ' abc/%/_/%///_% ' ESCAPE '/';
the above operations are for general data types, such as Char, VARCHAR2.
If there is nchar, NVARCHAR2, the above processing will appear ORA-01425 error.


If you change to the following wording, a ORA-01424 error occurs.
SELECT * from TBL WHERE COL like '%/_% ' ESCAPE to_nchar ('/')
The correct wording should be
SELECT * from TBL WHERE COL likec '%/_% ' ESCAPE to_nchar ('/')

Three, fuzzy query statement

CREATE TABLE Testsql (test_data VARCHAR2 () not NULL); SELECT * from Testsql;

It contains special characters,%,_,&amp, which may contain errors or need to find data that contains these characters.
Example 1, 4 kinds of fuzzy query to check the underline
1, sql> SELECT * from Testsql WHERE test_data like ' sdd_% ' ESCAPE ';

Where: The escape character is ' ' (empty one lattice);
2. sql> SELECT * from Testsql WHERE test_data like ' sdd/_% ' ESCAPE '/';

Where: The escape character is '/';

3. sql> SELECT * from Testsql WHERE test_data like ' sdd=_% ' escape ' = ';

Where: The escape character is ' = ';
4. sql> SELECT * from Testsql WHERE test_data like ' sddd_% ' escape ' d ';
Row not selected
The escape character is D, and the escape function is not implemented;

Similarly: Find all fields containing '% ' in this way:
Sql> SELECT * from Testsql WHERE test_data like '%/%% ' escape '/';

Example 2, but ' & ' cannot be found by escaping characters:
Sql> SELECT * from Testsql WHERE test_data like '%/&% ' escape '/'; *

can be escaped in a different way:
sql> SELECT ASCII (' & ') from dual;

Sql> SELECT * from Testsql WHERE test_data like '% ' | | Chr (38) | | %‘;

Oracle uses "| |" Making string connections

Example 3, ' escape:
Sql> SELECT * from Testsql WHERE test_data like '% '% ';

Example 4, the insertion of the special symbol "&"

Method One, sql> INSERT into Testinsert VALUES (' Test ' | | Chr (38) | | ');


Method Two, Sql>insert into Testinsert VALUES (' Tom ' | | ' & ' | | ' Jerry ')


Example 5, the insertion of a special symbol single quotation mark

Method one, Sql>insert into Testinsert VALUES (' Test ' | | ‘‘‘‘ );


Note: What does the "four single quotes" mean? First and last are the string connectors in Oracle, this is no objection. So what does the second ' and third ' mean? The second ' is an escape character. the third ' is our real content.

Method Two, Sql>insert into Testinsert VALUES (' Test ');

Method Three, Sql>insert into Testinsert VALUES (' Test ' | | chr (39) | | ');

oracle--wildcard, escape escape character, fuzzy query statement

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.