0

I have a requirement to parse the contents of a columns into rows on Oracle.

Input String:

"Honda","Civic"\n"Toyota","Camry"\n"Ford","Fusion"

Requirement 1: Convert the above string into separate rows based on the \n delimiter and load into a single column as shown below,

Column 1
------    
"Honda","Civic"  
"Toyota","Camry"  
"Ford","Fusion"

Can someone please help me with the syntax on Oracle?

Aleksej
  • 22,443
  • 5
  • 33
  • 38
Cleo
  • 21
  • 1
  • 5
  • 1
    Welcome to SO. Here you find something on [ask] and how to build a [mcve] that can be useful to improve your question. Above all, please show what you tried so far; questions like "how to do this", without showing any effort, are not usually welcome here – Aleksej Jan 03 '17 at 20:09
  • 1
    [This question](http://stackoverflow.com/questions/3819375/convert-comma-separated-string-to-array-in-pl-sql) has lots of good answers that I think are applicable here. Just substitute \n for , – Matt Jan 03 '17 at 20:39
  • Is the input exactly like that, with the characters `\n` used as separators? Then - do you just need to break them down as you demonstrated, don't you need to separate manufacturer and model in separate columns? (Perhaps you should!) –  Jan 04 '17 at 17:30
  • @Matt - of all the (many!) good answers using plain SQL, you chose to link to a thread where all the solutions create PL/SQL procedures, functions and packages? Note, too, that some answers that work with comma (or any other single-character identifier which is not expected to also appear in the "tokens") may not work with a separator like `\n`, at least not without some modifications. For example, `length(str) - length(replace(str, ',',''))` is often used to determine the number of tokens; that won't work here. –  Jan 04 '17 at 17:35

1 Answers1

0

Just for fun - here is a way to get the manufacturer and the model in separate columns, without the \n, the commas and the double-quotes. You may adapt the solution to get you the manufacturer and model, enclosed in double-quotes and separated by comma, in a single column, if indeed that is the requirement. (If that was meant as an intermediate step, you don't need it.) Note that I prefer to work with the double-quote character as a "marker" to avoid having to deal with \n - and you can do so, too, if you need to modify the solution below.

I assumed the strings are exactly in the format you show. I created a small test table with two rows to show how the solution works in that case; when there is a single row you may not run into some of the complications present when there are multiple rows, and I wanted a more general solution.

I wrote the solution using only standard string functions (no regular expressions), which should result in faster execution.

with
     test_data ( id, str ) as (
       select 1, '"Honda","Civic"\n"Toyota","Camry"\n"Ford","Fusion"' from dual union all
       select 2, '"Chevrolet","Volt"\n"Tesla","Model S"'              from dual
     )
--  end of test data; solution (SQL query) begins below this line
select id, 
       substr(str, 1 + instr(str, '"', 1, 1 + 4 * (level - 1)),
                   instr(str, '"', 1, 2 + 4 * (level - 1)) - 
                       instr(str, '"', 1, 1 + 4 * (level - 1)) - 1) as manufacturer,
       substr(str, 1 + instr(str, '"', 1, 3 + 4 * (level - 1)),
                   instr(str, '"', 1, 4 + 4 * (level - 1)) - 
                       instr(str, '"', 1, 3 + 4 * (level - 1)) - 1) as model_name
from   test_data
connect by level <= 1 + length(str) - length(replace(str, '\', ''))
       and prior id = id
       and prior sys_guid() is not null
;

 ID MANUFACTURER MODEL_NAME
--- ------------ ------------
  1 Honda        Civic
  1 Toyota       Camry
  1 Ford         Fusion
  2 Chevrolet    Volt
  2 Tesla        Model S

5 rows selected.