I am currently working on a problem whereby I have to parse through a string and split the string into 2 sub strings. My string can have multiple underscore( _ ) and I want the last underscore( _ ) to be the separator between the 2 sub strings.
Example:
If the text is ABC, it should return 'ABC' & NULL.
If it's ABC_12, it should return 'ABC' & '12'
If the text is ABC_12_pqr_97, it should return 'ABC_12_pqr' & '97'.
I tried LOCATE function to find the position of the last underscore but it only gives you the position of the 1st occurrence of the underscore. What I need is the position of the Nth occurrence of that underscore(which basically INSTR() function in SQL does). Is there any alternate way of deriving it?