Problem solve Get help with specific problems with your technologies, process and projects.

Separating county and state field into two columns

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

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.