Oracle INSTR function returns the position of the sub string string within a string. Function returns an integer value. It is an example of character functions.

Syntax:

INSTR(string, substring,[position],[place])

position and place are optional.

INSTR can be used in PL/SQL too.

Examples:-

1.select instr(‘sqlandplsql.com’,’.com’) as instrvalue from dual;

INSTRVALUE
——————–
12

Returns the position of sub string  ‘.com’ .

2. select instr(‘sqlandplsql.com’,’sql’,1,1) as instrvalue from dual;

INSTRVALUE
——————–
1

Returns the first occurrence of ‘sql’

3. select instr(‘sqlandplsql.com’,’sql’,1,2) as instrvalue from dual;

INSTRVALUE
——————–
9

Returns the second occurrence of ‘sql’

4. Use INSTR for string manipuation

Consider the table website with below records

No    Url

—       ——————————

1       http://www.yahoo.com
2       http://www.google.com
3       http://www.bbc.com
4       http://www.cnn.com
5       http://www.amazon.com
6       http://www.wikipedia.com

Below sql will return the website url text only ( Using both SUBSTR and INSTR functions)

select substr(url,instr(url,’.’,1,1)+1,instr(url,’.’,1,2) – (instr(url,’.’,1,1)+1) )  as urltext from website;

URLTEXT
——————–
yahoo
google
bbc
cnn
amazon
wikipedia