SUBSTR INSTR

SUBSTR

Oracle/PLSQL: SUBSTR Function
This Oracle tutorial explains how to use the Oracle/PLSQL SUBSTR function with syntax and examples.
Description
The Oracle/PLSQL SUBSTR functions allow you to extract a substring from a string.

Syntax
The syntax for the SUBSTR function in Oracle/PLSQL is:

SUBSTR( string, start_position [, length ] )
Parameters or Arguments
string
    The source string.
start_position
    The starting position for extraction. The first position in the string is always 1.
length
    Optional. It is the number of characters to extract. If this parameter is omitted, the SUBSTR function will return the entire string.

Note:
 If start_position is 0, then the SUBSTR function treats start_position as 1 (ie: the first position in the string).
 If start_position is a positive number, then the SUBSTR function starts from the beginning of the string.
 If start_position is a negative number, then the SUBSTR function starts from the end of the string and counts backwards.
 If length is a negative number, then the SUBSTR function will return a NULL value.
 
Applies To
The SUBSTR function can be used in the following versions of Oracle/PLSQL:
    Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Example
Let's look at some Oracle SUBSTR function examples and explore how to use the SUBSTR function in Oracle/PLSQL.

For example:
SUBSTR('This is a test', 6, 2)
Write Output
SUBSTR('This is a test', 6)
Write Output
SUBSTR('Chandra Sekhar', 1, 4)
Write Output
SUBSTR('Chadra Sekhar', -3, 3)
Write Output
SUBSTR('Chandra Sekhar', -6, 3)
Write ouput
SUBSTR('Chandra Sekhar', -8, 2)
Result: 'On'

INSTR

The Oracle INSTR function searches inside a string for a substring. The Oracle INSTR function works in a way similar to the SUBSTR function, but INSTR returns an integer number indicating the position of the substring within the string, rather than returning the substring itself.
Note: For more complex string handling functions, you can make use of Regular Expressions by using the extended version of the INSTR function named REGEXPR_INSTR.

Example Syntax:

INSTR( source_string, substring [, start_position [, occurrence ] ] )

Source_string is the string to be searched.

substring is the character string to be searched for inside of source_string.

Start_position is an optional argument. It is an integer value that tells Oracle where to start searching in the source_string. If the start_position is negative, then Oracle counts back that number of characters from the end of the source_string and then searches backwards from that position. If omitted, this defaults to 1.

Occurrence is an integer indicating which occurrence of substring Oracle should search for. That is, should INSTR return the first matching substring, the second matching substring, etc. This argument is optional. If omitted, it defaults to 1.

If the sub string is not found in source string, the Oracle INSTR function will return 0.

INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-')

Question on Instr/Sub string

There is a  path in obiee where it is showing as /shared/financials/. In this string,  want to get the value after second special character ('/') to before third special character ('/'). 

That means i should get the value "financials". 

Solution

select substr(:a,instr(:a,'/',1,2)+1,length (:a)) from dual



Notes On Date Data Type

YEAR    Year, spelled out
YYYY    4-digit year
YYY
YY
Y    Last 3, 2, or 1 digit(s) of year.
IYY
IY
I    Last 3, 2, or 1 digit(s) of ISO year.
IYYY    4-digit year based on the ISO standard
Q    Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM    Month (01-12; JAN = 01).
MON    Abbreviated name of month.
MONTH    Name of month, padded with blanks to length of 9 characters.
RM    Roman numeral month (I-XII; JAN = I).
WW    Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W    Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW    Week of year (1-52 or 1-53) based on the ISO standard.
D    Day of week (1-7).
DAY    Name of day.
DD    Day of month (1-31).
DDD    Day of year (1-366).
DY    Abbreviated name of day.
J    Julian day; the number of days since January 1, 4712 BC.
HH    Hour of day (1-12).
HH12    Hour of day (1-12).
HH24    Hour of day (0-23).
MI    Minute (0-59).
SS    Second (0-59).
SSSSS    Seconds past midnight (0-86399).
FF    Fractional seconds.

ex:
SELECT TO_CHAR(SYSDATE,'MON') FROM DUAL
It will return Month is this Format JAN,FEB,MAR,,,

Note while comparing need to TRUNC(SYSDATE) otherwise  data has Time stamp also

SQL functions
MIN
SELECT MIN(aggregate_expression)
FROM tables
WHERE conditions;
ex:
SELECT min(sal)
   FROM emp
it will return Minimum salary of the Employee table

MaX

SELECT MAX(aggregate_expression)
FROM tables
WHERE conditions;
ex:
SELECT MAX(sal)
   FROM emp
it will return Maximum salary of the Employee table

AVG
SELECT AVG(aggregate_expression)
FROM tables
WHERE conditions;

Example
SELECT AVG(sal)
   FROM emp
it will return Average salary of the Employees table
COUNT
Example
SELECT count(*)
   FROM emp
it will return no of employee of the Employees table