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!