19082

Split string in Oracle with regexp_substr in order

Question:

I have a string in Oracle database, my string is: 'bbb;aaa;qqq;ccc'

I used regexp for split my string:

select distinct trim(regexp_substr('bbb;aaa;qqq;ccc','[^;]+', 1,level) ) as q from dual connect by regexp_substr('bbb;aaa;qqq;ccc', '[^;]+', 1, level) is not null ;

I want to split it in order, I expected the below output always:

bbb aaa qqq ccc

because order of the subString are very important for me. but the result of this query is not in order:

qqq aaa bbb ccc

Answer1:

You don't need a DISTINCT to get your result; besides, to get the result in a given order, all you need is an ORDER BY clause:

select trim(regexp_substr('bbb;aaa;qqq;ccc','[^;]+', 1,level) ) as q from dual connect by regexp_substr('bbb;aaa;qqq;ccc', '[^;]+', 1, level) is not null order by level

Answer2:

If you do need DISTINCT:

WITH your_data( value ) AS ( SELECT 'bbb;aaa;qqq;ccc;aaa;eee' FROM DUAL ), positions ( string, lvl, start_pos, end_pos ) AS ( SELECT value, 1, 1, INSTR( value, ';', 1, 1 ) FROM your_data UNION ALL SELECT string, lvl + 1, end_pos + 1, INSTR( string, ';', 1, lvl + 1 ) FROM positions WHERE end_pos > 0 ), substrings ( string, substring, lvl, start_pos ) AS ( SELECT string, DECODE( end_pos, 0, SUBSTR( string, start_pos ), SUBSTR( string, start_pos, end_pos - start_pos ) ), lvl, start_pos FROM positions ) SELECT string, substring, lvl FROM substrings WHERE INSTR( ';' || string || ';', ';' || substring || ';' ) = start_pos;

<strong>Output</strong>:

STRING SUBSTRING LVL ----------------------- ----------------------- ---------- bbb;aaa;qqq;ccc;aaa;eee bbb 1 bbb;aaa;qqq;ccc;aaa;eee aaa 2 bbb;aaa;qqq;ccc;aaa;eee qqq 3 bbb;aaa;qqq;ccc;aaa;eee ccc 4 bbb;aaa;qqq;ccc;aaa;eee eee 6

Recommend

  • regular expression for line seperated different lengthy words
  • Parenthesis() and SQL Query Performance
  • Cannot page through all results using nextPageToken on YouTube search API v3
  • unexpected behaviour of Google BigQuery WHERE NOT list CONTAINS string
  • Extract All Possible Paths from Expression-Tree and evaluate them to hold TRUE
  • Cannot get text from text area
  • MySQL performance when updating row with FK
  • NHibernate manually control fetching
  • How to 'create temp table as select' in Slick?
  • How to get data from **Realm database** using **date object**?
  • Do query loads all the data in memory
  • Merging rows to columns
  • Zurb Foundation _global.scss meta styles for js?
  • PHP buffered output depending on server setting?
  • ActiveRecord query for a count of new users by day
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • Functions in global context
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • Breeze - Deleted Items nav properties bug
  • javaw.exe and eclipse startup problems
  • Can Jackson SerializationFeature be overridden per field or class?
  • Is possible to count alias result on mysql
  • Check if a string to interpolate provides expected placeholders
  • Deserializing XML into class C#
  • Redux, normalised entities and lodash merge
  • Display Images one by one with next and previous functionality
  • Function pointer “assignment from incompatible pointer type” only when using vararg ellipsis
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • Android Studio and gradle
  • SQL merge duplicate rows and join values that are different
  • AngularJs get employee from factory
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • need help with bizarre java.net.HttpURLConnection behavior
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • LevelDB C iterator
  • Authorize attributes not working in MVC 4
  • python draw pie shapes with colour filled
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • How can i traverse a binary tree from right to left in java?
  • How to Embed XSL into XML