• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Matt Kohanek
Village Idiot
Ranch Hand
Posts: 484
Java jQuery Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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)
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34870
369
Eclipse IDE Java VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 182
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Google Search may give you some idea and there exists usable pl/sql code among them.
 
Matt Kohanek
Village Idiot
Ranch Hand
Posts: 484
Java jQuery Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic