CODEWITHSUNDEEP
sqloracle-database
Sartori
Sartori

Reputation: 1

Oracle SQL - Reading delimiter

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.

Upvotes: 0

Views: 54

Answers (2)

Littlefoot
Littlefoot

Reputation: 142958

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?

Upvotes: 1

Mahamoutou
Mahamoutou

Reputation: 1555

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.

Upvotes: 0

Related Questions