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?
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.
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
