Substr () function and left-right link in Oracle

Source: Internet
Author: User
In this SQL statement, right join is used, that is, ldquo; (+) rdquo; the other side of the SQL statement is in the connection direction. All records on the right of the equal sign are displayed, whether it is on the left

In this SQL statement, right join is used, that is, ldquo; (+) rdquo; the other side of the SQL statement is in the connection direction. All records on the right of the equal sign are displayed, whether it is on the left

Substr () function: returns part of the string.
Syntax: substr (string, start, length)
Required: specifies where the string starts.
Positive number-start at the specified position of the string
Negative number-starting from the specified position at the end of the string
0-Start at the first character in the string

Example:
Substr ("ABCDEFG", 0); // return: ABCDEFG, intercepting all characters
Substr ("ABCDEFG", 2); // return: CDEFG, intercepting all characters starting from C
Substr ("ABCDEFG", 0, 3); // return: ABC, 3 characters starting from
Substr ("ABCDEFG", 0,100); // return: ABCDEFG, 100 although the maximum length of the pre-processed string is exceeded, the returned results are not affected. The system returns the maximum number of pre-processed strings.
Substr ("ABCDEFG", 0,-3); // return: EFG. Note that if the value of-3 is negative, the value starts from the end and the position of the string remains unchanged.

Left and right connections:
In the Oracle PL-SQL, left and right connections are implemented as follows
SELECT emp_name, dept_name
FORM Employee, Department
WHERE Employee. emp_deptid (+) = Department. deptid

This SQL statement uses the right connection, that is, the other side of the "(+)" is the connection direction. All records on the right side of the equal sign of the right connection are displayed, whether it is matched on the left or not, that is, whether a department does not have an employee in the previous example, the department name will appear in the query results.

Otherwise:
SELECT emp_name, dept_name
FORM Employee, Department
WHERE Employee. emp_deptid = Department. deptid (+) is the left join. no matter whether the Employee has a Department number that can be matched in the Department table, the Employee's record will be displayed.

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.