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

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.

Examples

Example 1,

Example 2,

Example 3,

Example 4,

Intriguing Question

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

INSTR

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

Syntax

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.

Examples

Example 1,

Example 2,

Example 3,

Example 4,

Oracle Documentation of SUBSTR

Oracle Documentation of INSTR

Hope you like the article and find it useful. We highly appreciate comments and feedback.

You can also Subscribe here to stay updated on latest posts of Oraclemine.com.

Hi I am Paras.

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!

Leave a Reply

Your email address will not be published. Required fields are marked *