0

I have the table as below.

Input

         + -------------------------------------+
         |   Country                | Code      |
         +--------------------------+-----------+
         | India,srilanka,Australia |  I,S,A    |
         +--------------------------+-----------+

I need a SQL query to select the value as following format in Oracle:

Output

    +-----------------+------+
    |  Country        | Code |
    +-----------------+------+
    | India           |   I  |
    | Srilanka        |   S  |
    | Australia       |   A  |
    +-----------------+------+
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sathyendran a
  • 1,709
  • 4
  • 21
  • 27
  • There isn't an existing function, you will need to create one – JohnHC Jan 19 '17 at 11:08
  • It's a good idea to get rid of that horrible table and create a new one with one row per country. (This is what you want to do, correct?) This may be easiest achieved by using some programming language (PL/SQL, C#, Java, PHP, ...): select the row from the table, scan it to build insert strings and insert the new records in the new table. – Thorsten Kettner Jan 19 '17 at 11:34
  • Never, ever store data as comma separated items! It will only cause you lots of trouble. – jarlh Jan 19 '17 at 11:46

0 Answers0