Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2009
    Posts
    19

    Unanswered: Count structured data

    Hi there, oracle newb so dont flame me please!... I am trying to pull some data out of a structured field that looks like this:

    ID_Structured

    1234.3456.3421.
    1234.2345.
    2345
    1234.3456.3421.
    4567.

    Etc...

    Each four digits represents a different part of the string, seperated by a period. so I would like to be able to could each time that function is found.

    So for the above set I would like to see the result:

    ID
    1234 3
    3456 1
    3421 2
    2345 2
    4567 1

    Was trying to do it using substr and instr but I cannot work it out.

    The same function cannot appear twice in a string.

    Any ideas?

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Code:
    select x, count(*) 
      from (select substr(ID_Structured, 1, 4) as x
              from table
             union all
            select substr(ID_Structured, 6, 4)
              from table
             where length(ID_Structured) > 5
             union all
            select substr(ID_Structured, 11, 4)
              from table
             where length(ID_Structured) > 10)
    group by x;

Posting Permissions

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