Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Unhappy Oracle Query to parse a column string with delimiter

    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.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,649
    when all else fails Read The Fine Manual
    INSTR
    SUBSTR
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  3. #3
    Join Date
    Oct 2010
    Posts
    7
    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

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,009
    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.

  5. #5
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Thumbs up Many thanks for suggestions

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •