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
