How to use substring with a delimiter



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.

Best Answer

  • 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