Oracle wildcard, use of operators, colons

Source: Internet
Author: User

The where comparison conditions are as follows:

Equal to: =, <, <=,>, >=, <>

>,<: Greater than, less

>=. <=: Greater than or equal to, less than or equal

=: Equal

! =, <>, ^ =: Not equal

Including: in, not in exists, not exists

Range: between... and, not between... and

Matching Test: like, not like

Null test: is null, is not null

Boolean links: and, or, not

Wildcard:

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 Supplier name, you can write the code as follows:

Select * from l_suppliers where supplier_name like '% ''%'

& 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.

Multi-line comment :/*......*/.

Not equal to: there are multiple expressions :! =, ^ =, <>, Not xxx = yyy, not (xxx = yyy)

--------------------------------------------------------------------------------

In Oracle. ":" Two interesting ideas;

1. assign a value to a variable. For example, names varchar2 (10): = 'A'. This assigns "aa" to the variable names.

2: Indicates reference. That is, the value corresponding to the field in the reference table.

For example, if the name in the emp table is "Bob", the value of old. name is "Bob ".

It indicates that a reference can only appear in a trigger.

Related Article

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.