City        Emp_Num_ID
Cleveland   2164445437/FTTD/JAMES, TYLER/139561151
Tokyo       1261120379/FTTD/BOYD, ADAMS/14468140  
Marakech    4049838896/FTTD/SMITH, TULY E/13956144
Houston     7980151429/FTTD/NEARY, HARTMAN/14215411

I'm trying to extract all digits after the third / with substring

select substr(Emp_Num_ID,/,10) as Emp_ID 
from Employee_M

sometimes we can have between 4 to 10 charterers after the third / so I chose 10 for the length.

any idea how to fix that.

  • you can use instr function to get the position of last /
    -1 = you are looking from the end of the string
  • than you add 1, so you start position will be the first number after /
  • you do not need the third parameter in substr if you need substring till the end of a string

    select substr(Emp_Num_ID,instr(Emp_Num_ID,'/',-1)+1) as Emp_ID from Employee_M

| improve this answer | |
  • thanks it worked like a charm, but when I try to compare that with another field I'm getting an error. select substr(Emp_Num_ID,instr(Emp_Num_ID,'/',-1)+1) as Emp_ID from Employee_M where substr(Emp_Num_ID,instr(Emp_Num_ID,'/',-1)+1)=City I'm getting "ORA-01722: invalid number" anyway that I can use this against another field – Driven Apr 17 '19 at 23:02
  • i have just tested and it works =>fiddle. – hotfix Apr 18 '19 at 5:24
  • here is also an exmple what could cause the invalid number exception. you have to check your data – hotfix Apr 18 '19 at 5:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.