Friday, February 5, 2010
Oracle SQL: Get last element from delimited list column
I tried to get last element from a delimited list column using Oracle SQL. Googling around found this http://www.psoug.org/reference/substr_instr.html?PHPSESSID=3bc7776a557faacca6d4ea23faba88b9, which got an example for this issue but only suitable for fixed list.
I made some modification so it can be use for dynamic list.
select
substr('abc,def,ghi,jkl',INSTR('abc,def,ghi,jkl','/', -1, 1)+1, length('abc,def,ghi,jkl')-INSTR('abc,def,ghi,jkl','/', -1, 1)) last_element from dual
Do share if you have shorter/better way :-)
I made some modification so it can be use for dynamic list.
select
substr('abc,def,ghi,jkl',INSTR('abc,def,ghi,jkl','/', -1, 1)+1, length('abc,def,ghi,jkl')-INSTR('abc,def,ghi,jkl','/', -1, 1)) last_element from dual
Do share if you have shorter/better way :-)
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment