selecting a specific part of a column from a database(oracle)

Question

the database i am working with has two specific columns that i am interested(among about 130+ other columns). the first column has a lot of different parameter names stored (for each row) separated by vertical bar ( | ) and the following column stores the values for each of those parameter separated by the vertical bar. so its like -

----------------------------------------------------------------------------------------------
Column 1                                       <|||>           Column 2
----------------------------------------------------------------------------------------------
par1 | par2 | par3 | par 4| par 5              <|||>  val1 | val2| val3| val4| val5
------

---

now, i am only interested with a specific parameter and it's corresponding value (say for example i just need the par4 and its value val4 ). What would be the most efficient way to do that? ADD: I am using ASP.NET(+ C#), I need to chart the result into my page. That is why I am specifically interested in single parameter and it's corresponding value.

PS: Moderators, I didnt really find the answer somewhere so I decided to post instead. In case the answer is already here somewhere then please close the thread and direct me to the solution, I dont intend to spam. Thanks. EDIT: I cannot create the tag for 'vertical bar'(says i need at least 1500 rep). In case moderators feel like this post requires that tag then please do the needful.

Answer 1

Oracle doesn't have a built-in string tokenizer but there are various ways to split a delimit string. This one uses regular expressions and the CONNECT BY clause.

with col1 as (
        select level as token_no
               , regexp_substr(col1, '[^\|]+', 1, level) as token
        from   t42
        connect by level <= regexp_count(col1, '\|') + 1 
        )
    , col2 as (
         select level as token_no
                , regexp_substr(col2, '[^\|]+', 1, level) as token
         from   t42
         connect by level <= regexp_count(col2, '\|') + 1 
        )
select col1.token as par
       , col2.token as val
from col1 
     join col2
         on col1.token_no = col2.token_no
where col1.token_no = 4
/

Here is the [inevitable SQL Fiddle][1].

Note: for the sake of clarity I have used CTE in this example. In real life you might want to turn this into a function.

---

On the subject of real life, storing arrays in concatenated strings like this is a bad practice. It is also a common one, alas.