how to use substring with a delimiter

Question

Employee_M

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.

Answer 1

• You can use the instr function to get the position of the last /
-1 means you are looking from the end of the string.

• Then you add 1, so your start position will be the first character after /.

• You do not need the third parameter in substr if you need the substring until the end of the string.

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

• Driven: 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
• hotfix: i have just tested and it works =>fiddle.
• hotfix: here is also an exmple what could cause the invalid number exception. you have to check your data