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

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