0

I am stuck with one of issue where I need to ensure split of string which will guaranteed the order of split string in which its passed . Example : string = "a,b,c,d,e"

After split expected guaranteed order result : Please see the attached image for result .

I seen many ans which will give me split of string but as we know temp table will not guaranteed the order of string . I need this for both SQL 2012 or higher,Oracle 10g should be working .enter image description here

MT0
  • 143,790
  • 11
  • 59
  • 117
user1920832
  • 27
  • 2
  • 8

1 Answers1

0

You are not going to find a single query that does this for both SQL Server and Oracle as, among many other reasons, their string-parsing functions have different names.

For Oracle, there are lots of solutions in this question; however, they mostly will not work on Oracle 10g as they rely on improvements that were introduced in 11g or 12c. This answer should be able to be adapted to [hopefully] make it work in Oracle 10g (but I have nowhere to test it as Oracle 10g is very old now and you should really be looking to upgrade to a newer version):

SELECT t.value,
       v.COLUMN_VALUE AS item
FROM   table_name t
       CROSS JOIN TABLE(
         CAST(
           MULTISET(
             SELECT REGEXP_SUBSTR( t.value, '[^,]+', 1, LEVEL )
             FROM   DUAL
             CONNECT BY REGEXP_SUBSTR( t.value, '[^,]+', 1, LEVEL ) IS NOT NULL
           )
           AS SYS.ODCIVARCHAR2LIST
         )
       ) v

Which, for the sample data:

CREATE TABLE TABLE_NAME ( value ) AS
SELECT 'a,b,c,d,e' FROM DUAL;

Outputs:

VALUE ITEM
a,b,c,d,e a
a,b,c,d,e b
a,b,c,d,e c
a,b,c,d,e d
a,b,c,d,e e

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117