Hi, I have a varchar column. The values are separated using a comma (,). For example, the column values are as follows:
'P1101Part1This is a part'
'P2101Part2This is a part number 2'
'P3101Part1.1This is a sub part for part 1' I would like to extract the last value from each column. With respect to the above example, the output should be as follows:
'This is a part'
'This is a part number 2'
'This is a sub part for part 1' I know that we should use substring function to do this, but I am not sure how I can find out the value after '' in the reverse direction. Thanks in advance.
(comments are locked)

I found the solution for this problem. I used Substr and Instr functions. If we specificy position of occurrance of '' as 1, the instr function will calculate the position in reverse direction.
(comments are locked)

select substr('P1101Part1This is a part',instr('P1101Part1This is a part','',1)+1) from dual
(comments are locked)

use regular expressions in oracle 10g and above .. here's the example with temp as ( select 'P1101Part1This is a part' STR from dual UNION ALL select 'P2101Part2This is a part number 2' STR from dual UNION ALL select 'P3101Part1.1This is a sub part for part 1' STR from dual ) SELECT regexp_substr(str,'[^]+$',1,1) STR from temp
(comments are locked)

use substr/instr functions for all versions of oracle.. here's the example with temp as ( select 'P1101Part1This is a part' STR from dual UNION ALL select 'P2101Part2This is a part number 2' STR from dual UNION ALL select 'P3101Part1.1This is a sub part for part 1' STR from dual ) select substr(str, instr(str, '', 1, 1)+1 ) STR from temp
(comments are locked)
