Here you go... replace indata with your column name WITH ABC AS (SELECT '12|4567|90|abc|def' indata FROM dual) SELECT substr(indata,instr(indata,'|',-1,3)+1,instr(indata,'|',-1,2)-1-instr(indata,'|',-1,3)) Last_but_3, substr(indata,instr(indata,'|',-1,2)+1,instr(indata,'|',-1,1)-1-instr(indata,'|',-1,2)) Last_but_2, substr(indata,instr(indata,'|',-1,1)+1) Lastelement from abc;
Here you go for MySQL SELECT substring_index(substring_index("abc|123|4456|789","|",-3),"|",1) AS FromlastPosition3, substring_index(substring_index("abc|123|4456|789","|",-2),"|",1) AS FromlastPosition2, substring_index(substring_index("abc|123|4456|789","|",-1),"|",1) AS FromlastPosition3;