Forum Stats

  • 3,815,828 Users
  • 2,259,096 Discussions
  • 7,893,264 Comments

Discussions

Cutting string on dots

2»

Answers

  • Arunkumar Ramamoorthy-Oracle
    Arunkumar Ramamoorthy-Oracle Member Posts: 5,880 Employee
    No Alex, it wont work ;) (the fifth row will be blank in that case).

    Figured out why after going through Frank's reply :-). Since there is no fifth . in the string, just append a . to the text in the instr made the trick :-).
  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    edited Oct 19, 2009 12:06PM
    It wasn't displaying the last part because you were outputting the entire string UNTIL the xth occurrence of a period. However since the string doesn't end in a period it wouldn't print out the last 5. However, if you artificially inject a period as I have done below, it works.
    SQL> WITH test_data AS
      2  (
      3          SELECT '14.10.5.1.5' AS STR FROM DUAL
      4  )
      5  select level, substr(str, 1, instr(str || '.','.',1,level)-1) from test_data
      6  connect by level<=(length(str || '.') - length(replace(str || '.','.','')))
      7
    SQL> /
    
         LEVEL SUBSTR(STR,
    ---------- -----------
             1 14
             2 14.10
             3 14.10.5
             4 14.10.5.1
             5 14.10.5.1.5
    Edited by: Centinul on Oct 19, 2009 12:06 PM

    The response would have been posted earlier if my Internet connection was a little more reliable :)
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    XMLTABLE using 10g...
    SQL> select trim(column_value) column_value 
      from xmltable('declare function local:f ($str) {
                        if (ora:matches($str, "\.\d+$")) then
                        (local:f(ora:replace($str, "\.\d+$", "")), $str)
                        else ($str)
                        };
                        local:f(//text())' passing xmlelement(e,'14.10.5.1.5')
                    )
    /
    COLUMN_VALUE                  
    ------------------------------
    14                            
    14.10                         
    14.10.5                       
    14.10.5.1                     
    14.10.5.1.5                   
    
    5 rows selected.
  • BluShadow
    BluShadow Member, Moderator Posts: 41,875 Red Diamond
    michaels2 wrote:
    XMLTABLE using 10g...
    SQL> select trim(column_value) column_value 
    from xmltable('declare function local:f ($str) {
    if (ora:matches($str, "\.\d+$")) then
    (local:f(ora:replace($str, "\.\d+$", "")), $str)
    else ($str)
    };
    local:f(//text())' passing xmlelement(e,'14.10.5.1.5')
    )
    /
    COLUMN_VALUE                  
    ------------------------------
    14                            
    14.10                         
    14.10.5                       
    14.10.5.1                     
    14.10.5.1.5                   
    
    5 rows selected.
    Holy Sh!t, recursion in an XMLTable/XQuery. You are just warped and twisted Michael. :D
  • 678284
    678284 Member Posts: 318
    Hi everyone.
    I want solution using unpivot :-)
  • BluShadow
    BluShadow Member, Moderator Posts: 41,875 Red Diamond
    bbbb wrote:
    Hi everyone.
    I want solution using unpivot :-)
    That's nice for you.
    Why don't you start your own thread, giving your database version, table structures, example data and expected output, as well as what you've tried yourself so far.
This discussion has been closed.