GeeCON Prague 2014*
The moose likes JDBC and the fly likes help extracting String from a long String delimited by ;'s Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "help extracting String from a long String delimited by ; Watch "help extracting String from a long String delimited by ; New topic
Author

help extracting String from a long String delimited by ;'s

Matt Kohanek
Village Idiot
Ranch Hand

Joined: Apr 04, 2009
Posts: 483

So I have a String in this format:

xxxxx;xxxxx;xxxxx;xxxxx;xxxxx;value_I_need;xxxxx

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?
ie


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
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30595
    
154

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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.
Fatih Keles
Ranch Hand

Joined: Sep 01, 2005
Posts: 182
Google Search may give you some idea and there exists usable pl/sql code among them.
Matt Kohanek
Village Idiot
Ranch Hand

Joined: Apr 04, 2009
Posts: 483

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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: help extracting String from a long String delimited by ;'s