I have a comma delimited file that I am trying to load into an Oracle table. I need to separate the first field in the file, county and state, into two columns. Is there a way to get the whole county (which may have several words/spaces etc.) in one column and then just the state in the next column? Examples:
Anne Arundel MD Comm. urb. de Montreal QC
I don't know of an easy way to do it as you load as I'm assuming you're loading it via SQL Loader. (If someone out there does know, submit it as a tip!) What I'd likely do is try to either 1) load it into a intermediate column (have a county_state column as well as the county and state columns separately) and then write a PL/SQL code block to parse the intermediate column and insert the values into the correct columns or 2) write a C, Java, Perl or other program (heck, you could even do it with PL/SQL if you really wanted to using UTL_FILE) parse the column and appropriately comma delimit it the way you want before trying to load it.
Either way, you need to determine your rules for separating the single column into its two parts. Once you have the rules, write a routine to parse the combined data out of the column into it's individual pieces. Whether you do this before or after the load is likely a matter of how big the file is and how complex the parsing routine will be.
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.