INSTR and SUBSTR functions in Oracle

Source: Internet
Author: User

INSTR and SUBSTR functions in Oracle

1. INSTR Functions

Syntax:
Instr (string1, string2 [, start_position [, nth_appearance])
Parameters:
String1: Source string to be searched in this string.
String2: string to be searched in string1.
Start_position: Position of string1. This parameter is optional. If omitted, the default value is 1. The string index starts from 1. If this parameter is positive, it is retrieved from left to right. If this parameter is negative, from right to left, the start index of the string to be searched in the source string is returned.
Nth_appearance: indicates the number of string2. this parameter is optional. If omitted, the default value is 1. If it is negative, an error is returned.

Example:
Select instr ('Hello World! ', 'L') FROM dual;
Result: 3
Select instr ('Hello World! ', 'L', 4, 2) FROM dual;
Result: 10
-- This function can be used to check whether String1 contains String2. If 0 is returned, it indicates that it does not contain; otherwise, it indicates that it contains
Select instr ('Hello World! ', 'World') FROM dual;
Result: 7

-- Assume that the employee table staff contains the employee ID, name, department, and occupation.
-- Retrieve the employee name, department, and occupation information of 'a10001 'and 'a10002' respectively.
SELECT code, name, dept, occupation FROM staff WHERE code IN ('a10001 ', 'a10002 ');
Or:
SELECT code, name, dept, occupation FROM staff WHERE code = 'a10001 'OR code = 'a10002 ';

Equivalent:
SELECT code, name, dept, occupation FROM staff WHERE instr ('a10001, A10002 ', code)> 0;
Note: Reduce the number of single quotes

-- Fuzzy query
SELECT code, name, dept, occupation FROM staff WHERE code LIKE '% 100 ';
Equivalent:
SELECT code, name, dept, occupation FROM staff WHERE instr (code, '001')> 0;

-------------------------------------- Split line --------------------------------------

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Oracle Spatial Database Function usage

Oracle random function DBMS_RANDOM

-------------------------------------- Split line --------------------------------------

2. SUBSTR Function
Syntax:
Substr (string, start_position, [length])
Parameters:
String: string to be truncated
Start_position: Start position of Truncation
Length: The Truncation length.

Example:
Select substr ('Hello World! ', 3) FROM dual;
Result: llo World!
Select substr ('Hello World! ', 3, 6) FROM dual;
Result: llo Wo
Select substr ('Hello World! ', 0) FROM dual;
Result: Hello World!
Select substr ('Hello World! ', 1) FROM dual;
Result: Hello World!
Select substr ('Hello World! ',-0) FROM dual;
Result: Hello World!
Select substr ('Hello World! ',-1) FROM dual;
Result :!
Select substr ('Hello World! ',-5) FROM dual;
Result: orld!

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.