Does Oracle have a package already that will let me do something like feed in the delimiter and the value I want at some point within those delimiters?
Or do I need to write the logic for this myself?
edit - If the package I am wondering about does not exist can someone give me an idea about how to write this myself, preferably so that it starts to evaluate the String from the end going towards the front - since I am trying to get the value close to the end there it seems more likely that starting from the end will have less chance to be introduce a stray semi colon or something else to throw things off)
True wisdom is in knowing you know nothing - Socrates
If using Java, I'd get the whole String via JDBC and then use string.split(). Assuming you need to do this in Oracle, have you looked at Oracle's regular expression package? Regular expressions are powerful and let you look for patterns well.
It is certainly doable in PL/SQL in a way Jeanne suggested. However, you should probably not do something like that at all.
The correct way to handle something like this in relational databases is to create a specific column for every value encoded in your string. You can then access and manipulate your values directly in SQL, PL/SQL or any other tool, you can index the columns if you ever need to search for a specific value (in your setting it would be a performance killer) and you can benefit from a range of various DB optimizations; and there are many in Oracle. Furthermore, in Oracle the storage requirements of the solution I suggest and your solution will probably be the same.
Martin Vajsar wrote:
The correct way to handle something like this in relational databases is to create a specific column for every value encoded in your string.
I agree Martin, undoubtedly.
However, I am working on top of someone else's code here so that choice is not mine - I can only work with what is already there.
Thanks to everyone for the suggestions. I have enough to go on now.
edit - here is the actual solution I used instead:
My new idea is to reverse the String, then extract the values, then re reverse the String, since the value I want is second to the last in the delimited list. That way there is less chance of an extra delimiter showing up to throw this whole thing off :p
subject: help extracting String from a long String delimited by ;'s