SQL - What is difference between SUBSTR and INSTR?

GeekInterview.com > Interview Questions > Oracle > SQL

Go To First | Previous Question | Next Question

SQL | Question 13 of 168

What is difference between SUBSTR and INSTR?

SUBSTR returns a specified portion of a string eg SUBSTR('BCDEF',4) output BCDE

INSTR provides character position in which a pattern is found in a string. eg INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-')

Total Answers and Comments: 7

Last Update: June 10, 2008

Sponsored Links

Best Rated Answer

—

Submitted by: ss

substr('BCDEF',4)=EF

Above answer was rated as good by the following members:
satyam_Ora, Rama Devi

July 07, 2005 11:34:04

RE: What is difference between SUBSTR and INSTR?

substr('BCDEF',4)=EF

Is this answer useful? Yes | No

1 0

Overall Rating: +1

February 06, 2006 08:01:33

srinivas22338

Member Since: February 2006 Contribution: 2

RE: What is difference between SUBSTR and INSTR?...

substr gives the substring of the position we gives where as

instr gives the the character present in the particular position

Is this answer useful? Yes | No

November 02, 2006 11:22:40

thumatinagaraju

RE: What is difference between SUBSTR and INSTR?...

hisubstr i s used to select pirticular word frm stringsuppose select substr('nagaraju',1,3) from dual ;returns nag means 1 is start position of word and 3 is end ,,,ok instr returns only position of first occureence of a specified characterselect instr('nagaraju','a') from dual ;returns 2 only ;;ok i hope usatisfied with my answers

Is this answer useful? Yes | No

January 29, 2007 03:49:21

Rohan Deshpande

RE: What is difference between SUBSTR and INSTR?...

SUBSTR: it extracts the string of determined length.whereras (i.e. it returns character for specified position)INSTR:it returns the number of named string.(i.e. it returns number )

Is this answer useful? Yes | No

April 05, 2007 00:39:41

i.v.praveen

RE: What is difference between SUBSTR and INSTR?...

SUBSTR returns a character where as INSTR returns number

Is this answer useful? Yes | No

August 18, 2007 07:08:00

syam sundar

RE: What is difference between SUBSTR and INSTR?...

substr:
sub(string,n,m): It returns the characters from nth position, m
characters long
Ex:-substr(abcdefgh,3,3) it returns cde

instr:

instr(string,n,m):It returns the position of the nth character of the mth
occurrence.
Ex:-instr(abcadea,1,3) it returns 7

Is this answer useful? Yes | No

1 0

Overall Rating: +1

June 10, 2008 03:22:02

ashish88

Member Since: June 2008 Contribution: 8

RE: What is difference between SUBSTR and INSTR?

hi Buddy,

Substr only give the sub part of the string
Substr(String,'start postion','length')
Substr('ABCDEFAG',3,7); /*out put is =cdefag */

Instr to give only possion of letter in use in a string
Instr(String,'letter',possition of letter in use)
Instr('ABCDEFAB','a',1,2)/* out put is= 1

Is this answer useful? Yes | No

Go To Top