Home » SQL & PL/SQL » SQL & PL/SQL » Help with REGEXP_SUBSTR (Oracle 10g)
Help with REGEXP_SUBSTR [message #659442] Tue, 17 January 2017 07:02 Go to next message
adfnewbie
Messages: 38
Registered: January 2016
Member
Hi Friends,

I am looking for a way to convert comma separated string into columns or rows.

Example: I have a varchar2 variable which will store 'file1,file2,file3' and I need to split these values and use file1 and file2 and file3 separately. These is no fixed number of values in csv string. it can be file1,file2,.....,filen

I have tried below:

SELECT regexp_substr('file1,file2,file3','[^,]+', 1, level)
FROM dual
CONNECT BY regexp_substr('file1,file2,file3', '[^,]+', 1, level) IS NOT NULL

This gives me 3 rows for file1,2 and 3. But I don't want to hard-code the string here, instead I want to pass a variable which holds the csv string.


Any help is highly appreciated.

Thanks,
RC
Re: Help with REGEXP_SUBSTR [message #659443 is a reply to message #659442] Tue, 17 January 2017 07:20 Go to previous messageGo to next message
Littlefoot
Messages: 21328
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, you could use a substitution variable, such as
SQL> l
  1      SELECT REGEXP_SUBSTR ( '&&string',
  2                            '[^,]+',
  3                            1,
  4                            LEVEL)
  5        FROM DUAL
  6  CONNECT BY REGEXP_SUBSTR ( '&&string',
  7                            '[^,]+',
  8                            1,
  9                            LEVEL)
 10*               IS NOT NULL
SQL> /
Enter value for string: file1,file2,file3
old   1:     SELECT REGEXP_SUBSTR ( '&&string',
new   1:     SELECT REGEXP_SUBSTR ( 'file1,file2,file3',
old   6: CONNECT BY REGEXP_SUBSTR ( '&&string',
new   6: CONNECT BY REGEXP_SUBSTR ( 'file1,file2,file3',

REGEXP_SUBSTR('FI
-----------------
file1
file2
file3

SQL>
Or, using DEFINE:
SQL> define string='little,fo,ot'
SQL>     SELECT REGEXP_SUBSTR ( '&string',
  2                            '[^,]+',
  3                            1,
  4                            LEVEL)
  5        FROM DUAL
  6  CONNECT BY REGEXP_SUBSTR ( '&string',
  7                            '[^,]+',
  8                            1,
  9                            LEVEL)
 10                IS NOT NULL;
old   1:     SELECT REGEXP_SUBSTR ( '&string',
new   1:     SELECT REGEXP_SUBSTR ( 'little,fo,ot',
old   6: CONNECT BY REGEXP_SUBSTR ( '&string',
new   6: CONNECT BY REGEXP_SUBSTR ( 'little,fo,ot',

REGEXP_SUBST
------------
little
fo
ot

SQL>
Or some other technique, which probably depends on a tool you use.
Re: Help with REGEXP_SUBSTR [message #659445 is a reply to message #659443] Tue, 17 January 2017 07:27 Go to previous messageGo to next message
adfnewbie
Messages: 38
Registered: January 2016
Member
Thanks Littlefoot!

Now since this gives me a set of rows, can I fetch all these records in a cursor/collection and loop through use the values?

-RC
Re: Help with REGEXP_SUBSTR [message #659446 is a reply to message #659445] Tue, 17 January 2017 07:48 Go to previous messageGo to next message
BlackSwan
Messages: 25793
Registered: January 2009
Location: SoCal
Senior Member
It is a Bad Thing to store more than 1 value in a single column/variable.
Re: Help with REGEXP_SUBSTR [message #659447 is a reply to message #659446] Tue, 17 January 2017 08:05 Go to previous messageGo to next message
adfnewbie
Messages: 38
Registered: January 2016
Member
BlackSwan,

There is no workaround to this - User will provide a list of file names separated by commas.. It will come as a parameter from a concurrent program

-RC
Re: Help with REGEXP_SUBSTR [message #659449 is a reply to message #659447] Tue, 17 January 2017 08:18 Go to previous messageGo to next message
BlackSwan
Messages: 25793
Registered: January 2009
Location: SoCal
Senior Member
do these files reside on the DB Server or the client system?
Re: Help with REGEXP_SUBSTR [message #659450 is a reply to message #659449] Tue, 17 January 2017 09:34 Go to previous message
Bill B
Messages: 1709
Registered: December 2004
Senior Member
TRY

>CREATE TABLE WHB (VAL VARCHAR2(500));

Table created.

 >INSERT INTO WHB VALUES('FILE1,FILE2,FILE3,FILE4,FILE5')

1 row created.

>COMMIT;

Commit complete.

 SELECT trim(regexp_substr(t.VAL, '[^,]+', 1, lines.column_value)) text
    FROM WHB T,
    TABLE (CAST (MULTISET
    (SELECT LEVEL FROM dual
      CONNECT BY regexp_substr(t.VAL , '[^,]+', 1, LEVEL) IS NOT NULL
      ) AS sys.odciNumberList ) ) lines
   ORDER BY lines.column_value;

TEXT
--------------------------------------------------------------------------------
FILE1
FILE2
FILE3
FILE4
FILE5
Previous Topic: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP2
Next Topic: unable to get data from V$SESSION table
Goto Forum:
  


Current Time: Wed Jan 17 05:59:11 CST 2018

Total time taken to generate the page: 0.01859 seconds