Introduction

This tutorial explains how to use Oracle INSTR function with basic syntax and examples.

INSTR function Search for particular sub-string in string and then return the location of the string in number position.

Syntax:

INSTR( string, sub-string [, start_position [, appearance ] ] )

Name	Description	Data Types
String	A string or character to search inside.	CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
sub-string	A sub-string that you want to search about.	CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
start_position	The Position in string where the search start with.	NUMBER, or any data type that can be implicitly converted to NUMBER, and must resolve to an integer.
appearance	The number of string appearance in the string	NUMBER, or any data type that can be implicitly converted to NUMBER, and must resolve to an integer.

Note:

• If start_position is omitted, then function treats start_position as number of 1.
• If start_position is a positive number, then function starts from the beginning of character given.
• If start_position is a negative number, then function starts from the end of character given and counts backwards.
• If appearance is omitted, then function default to 1 that means it will return the position of first appearance of sub-string.

Examples:

Let’s get some examples about Oracle INSTR and how to use it:

SELECT INSTR('It is Oraask.com',’s’)
FROM DUAL;

SELECT INSTR ('It is Oraask.com', ’s’, 1, 1)
FROM   dual;

SELECT INSTR ('It is Oraask.com', ’s’, 1, 2)
FROM   dual;

SELECT INSTR ('It is Oraask.com', ’s’, -1, 2) "Reversed Instring"
FROM   dual;

Similar functions to Oracle INSTR:

SUBSTR – extracts a sub-string from a string .
TRIM – delete space or specified character from start , end or both of a string.

Hope This Help.