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 |
|
adfnewbie
Messages: 54 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 |
|
Littlefoot
Messages: 21811 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Well, you could use a substitution variable, such asSQL> 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 #659450 is a reply to message #659449] |
Tue, 17 January 2017 09:34 |
Bill B
Messages: 1971 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
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:01:31 CDT 2024
|