We are in oracle 10g and Linux server. I need to parse a column string in a table such as below:
columnstring values
abcd|12|AB|2009-10-10 20:23:12
xyzd|1|ABC|2009-10-10 20:22:12
123456|12|ABD|2009-10-10 20:22:14

I need to parse the string and cump into a temp table with columns a1(char(20)), a2 char(3), a3 char(3), a4 datetimestamp. The delimiter in the string is a PIPE sign. How can I do this in Oracle SQL/Plus query? Any help is much appreciated. I tried substr functions but do not know how to parse string with delimiter and get different lengths as a column and dump into a table.

Thanks in advance.

when all else fails Read The Fine Manual
INSTR
SUBSTR

Say your string is in a column called "string_col".

The first four elements can be pulled out with:

select
substr(string_col, 1, pos_1st_pipe - 1),
substr(string_col, pos_1st_pipe + 1, pos_2nd_pipe - pos_1st_pipe - 1),
substr(string_col, pos_2nd_pipe + 1, pos_3rd_pipe - pos_2nd_pipe - 1),
substr(string_col, pos_3rd_pipe + 1, len(string_col) - pos_3rd_pipe - 1)
from
your_table

In the above you need to replace my pseudo variables with a call to instr i.e.
pos_1st_pipe : instr(string_col, '|', 1, 1)
pos_2nd_pipe : instr(string_col, '|', 1, 2)
etc....

Obviously these are all string values. You will probably need to use the TO_DATE function on the date value, though you could try to see if Oracle will cast implicitly from that format.

Techonthenet has a good basic function guide.

Dave

Are these values (such as "abcd|12|AB|2009-10-10 20:23:12"), by any chance, stored in a file? (I don't think so, you said that you need to parse a column string). However, if it is a case, then SQL*Loader would do that easily.

Littlefoot suggestion is a good idea to dump into a file and then use SQLLoader utility. May be I totally forgot that utility (dumb me). Dav's suggestion really helped me. I was struggling to get the second column from "select" statement ( I got the first one easily). His clue helped me to complete the query. Many thanks for teaching it.