Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 394 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
Cutting string on dots
Answers
-
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 :-). -
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 -
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.
-
michaels2 wrote:Holy Sh!t, recursion in an XMLTable/XQuery. You are just warped and twisted Michael.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.
-
Hi everyone.
I want solution using unpivot :-) -
bbbb wrote:That's nice for you.
Hi everyone.
I want solution using unpivot :-)
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.