CODEWITHSUNDEEP
sqloracle-databasefunction
The_Bear
The_Bear

Reputation: 173

SUBSTR and INSTR SQL Oracle

I've started using SUBSTR and INSTR in Oracle but I got confused when I came across this.

SELECT PHONE, SUBSTR(PHONE, 1, INSTR(PHONE, '-') -1)
FROM DIRECTORY;

So I know SUBSTR cuts values off, and INSTR shows where the occurrence is but the example I've put above has confused me, because the result it 362. When my original value was 362-127-4285. How does that work?

Upvotes: 3

Views: 93099

Answers (3)

ANAND
ANAND

Reputation: 1

SQL>select substr('[email protected]',1,instr('[email protected]','@')-1) from dual;

Upvotes: 0

Pragyan
Pragyan

Reputation: 61

SELECT PHONE, SUBSTR(PHONE, 1, INSTR(PHONE, '-',1,1) -1)
FROM DIRECTORY;

INSTR(String,'char_Search','position from where start searching','occurance')

If We are not giving 'starting position' and 'occurrence' it bydefault take as 1,1.

So bydefault it take as INSTR(phone,'-',1,1) --sql returns 1st occurance of string'-' position is 4 and then SUBSTR(PHONE, 1, 4 - 1) which is SUBSTR(PHONE, 1, 3).

SUBSTR(String,'starting position','no of character') SUBSTR(PHONE, 1, 3) --gives the substring of the PHONE column from the 1st that has length of 3 chars which is 362

Ex: using this value--'362-127-4285'

SELECT '362-127-4285' Example,
SUBSTR('362-127-4285',1,INSTR('362-127-4285','-')-1) Result 
FROM DUAL

Upvotes: 1

forpas
forpas

Reputation: 164089

INSTR(PHONE, '-') gives the index of - in the PHONE column, in your case 4
and then SUBSTR(PHONE, 1, 4 - 1) or SUBSTR(PHONE, 1, 3)
gives the substring of the PHONE column from the 1st that has length of 3 chars which is 362,
if the value PHONE column is 362-127-4285.

Upvotes: 8

Related Questions