Forum Stats

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

Discussions

Categories

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 :-).
    · Share on TwitterShare on Facebook
  • 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 :)
    · Share on TwitterShare on Facebook
  • 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.
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • 678284
    678284 Member Posts: 318
    Hi everyone.
    I want solution using unpivot :-)
    · Share on TwitterShare on Facebook
  • 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.
    · Share on TwitterShare on Facebook
This discussion has been closed.