Use the Oracle query wildcard for common characters

Every time fox appears in an Oracle script, you are required to provide a value for it. When you use fox, you only need to provide variable values for it when it appears for the first time. If you want

Every time fox appears in an Oracle script, you are required to provide a value for it. When you use fox, you only need to provide variable values for it when it appears for the first time. If you want

In the where clause, wildcards can be used with the like condition. In Oracle:

% (Percent): represents any number of characters, or may have no characters at all.

_ (Underline): indicates the exact unknown character.

? (Question mark): used to indicate the exact unknown character.

# (Well number): used to indicate the exact Arabic number, ranging from 0 to 9.

[A-d] (square brackets): used to indicate the character range from a to d.

Single quotes ('): in Oracle, only single quotes should be used to enclose text, characters, and dates. No quotation marks (including single double quotes) can be used to enclose numbers.

Double quotation marks ("): in Oracle, single double quotation marks have different meanings. Double quotation marks are used to enclose column aliases that contain specific characters or spaces. Double quotation marks are also used to place text in the date format.

Marker ('): in Oracle, the marker can also be written as two adjacent single quotes. In order to find all vendor names with marker in the middle of the vendor name, you can write code like this

& Symbol: in Oracle, & symbol is often used to indicate a variable.

For example, & fox is a variable, a little different & fox. Every time & fox appears in an Oracle script, you are required to provide a value for it. To use & fox, you only need to provide the variable value when & fox appears for the first time. If you want to use & as a common symbol, you should disable this feature. To disable this feature, run the following command: set define off, which is a SQLplus command, not an SQL command. SQLplus sets the environment in which SQL runs in Oracle.

Double vertical bars (|): Oracle uses double vertical bars to represent string connection functions.

Asterisk (*): select * indicates that all columns are selected, and count (*) indicates that all rows are calculated. When a wildcard is used, it indicates 0 or any number of characters.

Forward slash (/): Used to terminate an SQL statement in Oracle. More accurately, it indicates "running SQL code in the buffer zone ". A forward slash is also used as a separator.

SQL> select * from test;

ID NAME
------------------------------
1 baaab
2 ba_ AB
3 ba % AB

SQL> select * from test where name like '% _ % ';

ID NAME
------------------------------
1 baaab
2 ba_ AB
3 ba % AB

SQL> select * from test where name like '% \ _ %' escape '\';

ID NAME
------------------------------
2 ba_ AB

Therefore, when using some specific wildcards in oracle as common characters, do not forget to add the escape keyword. Its function is as follows:

1. Use the ESCAPE keyword to define the ESCAPE character. In mode, when an escape character is placed before a wildcard, the wildcard is interpreted as a common character.

2. ESCAPE 'escape _ character'

You can search for wildcards in strings instead of using them as wildcards. Escape_character is a character that represents this special purpose before a wildcard.

Select * from a WHERE name LIKE '%/% AB' ESCAPE '/'

,