Learn Oracle Database and Applications Here

SUBSTR and INSTR in Oracle

Let us learn two important standard functions SUBSTR and INSTR of SQL that you will use extensively while writing queries. They are easy to understand but sometimes we get bit of confused while actually implementing them.

What are SUBSTR and INSTR function?

SUBSTR

To select a part of a string from whole, SUBSTR function is used.

Syntax

SUBSTR(string_value, start_position, [substring_characters…]);

Here,

string_value = String from which the substring is to be derived start_position= Start position of substring

Note 1: If start_postition is given positive value, string search will start from left hand side. If it is negative value, string search will start from right hand side.

substring_characters = This parameter is optional and it returns number of characters from start position of substring. If no value is mentioned it will return characters from start position of substring to the end of actual string.

Note 2: If substring_characters is negative value, SUBSTR will return NULL as output.

Intriguing Question

What will be the result if I execute below query? Think and post your answers in comment

SELECT SUBSTR('ORACLEMINE' , 0 ) Substring_Result FROM DUAL;

INSTR

To find out position of a character or group of characters from a given string, INSTR function is used.

Syntax

INSTR(string_value, substring, [position…], [occurrence…]);

Here,

string_value = Actual string to be searched for substring = Substring to be searched from actual string position = This is an optional parameter. It indicates the position from which search of substring is to be carried out.

Note 3: If position is negative, INSTR searches from right hand side of the string. If it is positive, INSTR searches from left hand side. By default position is 1.

occurrence = This is an optional parameter that indicates occurrence of substring in actual string. The default value is 1. If no value is provided it searches for first occurrence of substring.

Note 4: If occurrence is given negative value, “argument is out of range” error is thrown.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!