Oracle SQL - Reading delimiter-CodePudding

I'm developing a command in Oracle SQL using a table that has that type of row:

company=1&product=12588&version=1

For my command, I need the product number and version of each row, but separated by columns.

My first question: How can I read only the product number, using something different of REGEXP_SUBSTR

My second question: What is the best way to create a new column to show the version without duplicate the line?

I hope someone can help me.

CodePudding user response：

To answer your first question, you can use sql below by combining only the ** substr ** and ** instr ** functions. This solution takes advantage of the four and last parameter of the instr function.

select your_column
, substr(your_column
          , instr(your_column, '=', 1, 1)   1
          , instr(your_column, '&', 1, 1) - instr(your_column, '=', 1, 1) - 1 
   ) company
, substr(your_column
          , instr(your_column, '=', 1, 2)   1
          , instr(your_column, '&', 1, 2) - instr(your_column, '=', 1, 2) - 1 
   ) product
, substr(your_column
          , instr(your_column, '=', 1, 3)   1 
  ) version
from (
  select 'company=1&product=12588&version=1' your_column from dual union all
  select 'company=2&product=52361&version=4' your_column from dual
) Your_data
;

demo

But, I'm not sure I understood your second question correctly.

CodePudding user response：

If data really is that simple, regular expressions make code rather simple. Way simpler than SUBSTR INSTR option. Why don't you want to use regex? Because it is evil, or ...?

sample data in lines #1 - 4
product: take the 2nd numeric value from the column
version: take the last numeric value from the column

SQL> with test (col) as
  2    (select 'company=1&product=12588&version=1' from dual union all
  3     select 'company=2&product=52361&version=4' from dual
  4    )
  5  select col,
  6    regexp_substr(col, '\d ', 1, 2) as product,
  7    regexp_substr(col, '\d $')      as version
  8  from test;

COL                               PRODUCT    VERSION
--------------------------------- ---------- ----------
company=1&product=12588&version=1 12588      1
company=2&product=52361&version=4 52361      4

SQL>

As of

What is the best way to create a new column to show the version without duplicate the line?

I have no idea what that means. "Without duplicate the line"? Which line? Which duplicate?