I want to be able to split csv strings in Oracle 9i
I've read the following article http://www.oappssurd.com/2009/03/string-split-in-oracle.html
But I didn't understand how to make this work. Here are some of my questions pertaining to it
- Would this work in Oracle 9i, if not, why not?
- Is there a better way of going about splitting csv strings then the solution presented above?
- Do I need to create a new type? If so, do I need specific privilages for that?
- Can I declare the type w/in the function?
It sounds like you don't want to add schema (types, function). One SQL only way to parse the delimited text is to 'go crazy' with instr and substr calls.
If your looking to formalize a structure and adding the appropriate application code (functions, views, types etc...) I would take a look at Tom Kyte's writing on this subject.
I used this in the end
Joyce,
Here are three examples:
1) Using dbms_utility.comma_to_table. This is not a general purpose routine, because the elements should be valid identifiers. With some dirty tricks we can make it work more universal:
2) Using SQL's connect by level. If you are on 10g or higher you can use the connect-by-level approach in combination with regular expressions, like this:
3) Again using SQL's connect by level, but now in combination with good old SUBSTR/INSTR in case you are on version 9, like you are:
You can see some more techniques like these, in this blogpost: http://rwijk.blogspot.com/2007/11/interval-based-row-generation.html
Hope this helps.
Regards, Rob.
To address your comment:
An example of inserting the separated values into a normalized table.
First create the tables:
Because you seem interested in the dbms_utility.comma_to_table approach, I mention it here. However, I certainly do not recommend this variant, because of the identifier quirks and because of the slow row by row processing.
I do recommend this single SQL variant:
Regards, Rob.
You might want to be a bit clearer on what you want to do, then we can give you a specific answer. Showing some of your code is always helpful :)
If you are using paramters, to split a string of csv numbers (eg: 1,2,3,4) then use that in a
IN
statement have a look at the functionstr2tbl()
in Question 670922. With a few changes you could change it to aVARCHAR2
or whatever you need.In the following you could set
:sMyCatagories
equal to'1,2,3,4'
and using it in a select statement....
This is really only useful if you are using parameters. If you are munging together SQL in your application, then just use a normal IN statement.
Here's a string tokenizer for Oracle that's a little more straightforward than that page, but no idea if it's as fast:
You can use it like this: