0

Can anyone please help me to split the following string 

'Column1, To_date(Column2, 'DD/MM/YYYY'), Column3'

into

COLUMN_LIST 

Column1     
To_date(Column2, 'DD/MM/YYYY')  
Column3

in oracle

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Tapas
  • 1
  • you'd better sharing sample data and expected output explicitly with us. – Barbaros Özhan Aug 25 '18 at 08:08
  • You would need to use dynamic SQL for this -- PL/SQL and `execute immediate`. – Gordon Linoff Aug 25 '18 at 11:23
  • To find how to split strings in Oracle on this website, Google for the following phrase (including the site: portion): **Split string into rows site:stackoverflow.com** You will find many (MANY!) results; this is one of the most common questions asked here. Obviously many people aren't used to do some research on their own before asking a question. –  Aug 25 '18 at 14:38
  • @GordonLinoff - I don't see it. What is dynamic in this OP's request? I see a simple tokenization of an input string - he/she is not asking to create new **columns** based on actual text in the input string, only new rows. –  Aug 25 '18 at 14:39
  • 2
    @mathguy . . . I'm pretty sure the OP wants the *results* of those expressions, not just to split the string. – Gordon Linoff Aug 25 '18 at 15:17
  • 1
    @mathguy - I don't think this is a duplicate of the question you proposed. The comma in the `To_date(Column2, 'DD/MM/YYYY')` token means a simple "split on comma" solution won't produce the result the OP wants. – APC Aug 25 '18 at 15:48
  • @Tapas - does your input really look exactly as you posted? Because the comma in the middle of `To_date(Column2, 'DD/MM/YYYY')` is going to make any solution extremely complicated. Normally a CSV file (e.g. exported from Excel") would have wrapped the tokens (cells) in field delimiters e.g. `'Column1, "To_date(Column2, 'DD/MM/YYYY')", Column3` precisely to prevent commas in the data colliding with the separator. – APC Aug 25 '18 at 15:53
  • @APC - good point, thank you for reopening the question. –  Aug 25 '18 at 16:56

1 Answers1

0
SELECT TRIM(regexp_substr(a.text, a.regExp, 1, level, null, 2)) names
FROM (SELECT 'Column1, to_date(Column2, ''DD/MM/YYYY''), Column3, TO_NUMBER(''123'', ''999''), Column4,Column5' text
           , '(^|,)(([^,]*\([^\(\)]*\))|[^\(\),]*)' regExp
        FROM dual) a
CONNECT BY regexp_substr(a.text, regExp, 1, level, null, 2) is not null

result is:
Column1
to_date(Column2, 'DD/MM/YYYY')
Column3
TO_NUMBER('123', '999')
Column4
Column5

I hope this is what you needed :)

OjtamOjtam
  • 280
  • 2
  • 8