SUBSTR and INSTR SQL Oracle

user_10659918 0
Viewed 49845

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?

function oracle sql
3 Answers

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.

user_104988280 
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
user_102185430 
SQL>select substr('user@example.com',1,instr('user@example.com','@')-1) from dual;
user_169177530
Related