Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2012
    Posts
    37

    Unanswered: Count the data from field seperator

    Hello All
    I have a string which looks like
    1112:1122::33445:556:334::221::::22121

    In short the string is separated by ":" I want to count the number of values between ":"

    like in above case I should get the result as 7

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Database version?

    On 11g it is a rather simple task:
    Code:
    SQL> select regexp_count('1112:1122::33445:556:334::221::::22121', '\d+') cnt from dual;
    
           CNT
    ----------
             7
    
    SQL>

  3. #3
    Join Date
    Mar 2012
    Posts
    37
    In this there is one issue. Let's say if the data is in format
    11.12:1122::33.445:55.6:334::221::::22121

    It gives wrong count, But it should give count as 7

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    In short the string is separated by ":" I want to count the number of values between ":"
    Are the string data separated by ":" always numeric?

    For example, is this possible?
    11.12:11aa22::b33.445:5.5.6:334c::22,1:::::::22121 :::xyz:?:::::+-0:::

    If the data was possible, was the answer 10?

  5. #5
    Join Date
    Mar 2012
    Posts
    37
    THREE cases are possible

    Case1 --> 001:001.2:00.11:23223.21:::
    Case2 --> 001:002:003:004:005::::
    Case3 --> 001.1:002.2:003.3:004.3:00.11::::

  6. #6
    Join Date
    Dec 2007
    Posts
    253
    Describe the rules behind the cases rather than simply posting examples.

  7. #7
    Join Date
    Mar 2012
    Posts
    37
    There is no specify rules. Only the upper cases are possible. That's it. Apart from that no case can exist

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Code:
    SQL> with test as
      2    (select '1112:1122::33445:556:334::221::::22121' col from dual union
      3     select '11.12:1122::33.445:55.6:334::221::::22121' from dual
      4    )
      5  select col, regexp_count(col, '(\d|\.)+') cnt from test;
    
    COL                                              CNT
    ----------------------------------------- ----------
    11.12:1122::33.445:55.6:334::221::::22121          7
    1112:1122::33445:556:334::221::::22121             7
    
    SQL>

  9. #9
    Join Date
    Mar 2012
    Posts
    37
    OR we can use
    select regexp_count('1112:1122::33445:556:334::221::::221 21', '\:+') cnt from dual;

    THIS ALSO WORKED FOR ME

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, it depends on what "worked" means to you, because your "solution" returns a result that is different from what you wanted.
    Code:
    SQL> with test as
      2        (select '1112:1122::33445:556:334::221::::22121' col from dual union
      3         select '11.12:1122::33.445:55.6:334::221::::22121' from dual
      4        )
      5  select regexp_count(col, '\:+') cnt from test;
    
           CNT
    ----------
             6
             6
    
    SQL>
    Where's "7" you specified earlier?

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by goodman2253 View Post
    There is no specify rules. Only the upper cases are possible. That's it. Apart from that no case can exist
    Do you mean(or want to say) that a string like the following is possible?

    11.12:11AA22::B33.445:5.5.6:334C::22,1:::::::22121 :::XYZ:?:::::+-0:::
    Last edited by tonkuma; 12-24-13 at 12:31.

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    11.12:11AA22::B33.445:5.5.6:334C::22,1:::::::22121 :::XYZ:?:::::+-0:::
    There was 10 values separate by ":" in the string, like...
    11.12
    11AA22
    B33.445
    5.5.6
    334C
    22,1
    22121 (including one trailing blank)
    XYZ
    ?
    +-0

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by goodman2253 View Post
    There is no specify rules. Only the upper cases are possible. That's it. Apart from that no case can exist
    I'm sorry, I'm not so well at English.
    So, I couldn't know the meaning of "no case can exist", and ignored the sentence.

    If the sentense had some essentials on your issue, would you explain the reason with more cocrete ways(or examples)?

Posting Permissions

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