I have a column as below. there are multiple columns like this with different data.
I need output like this
I need values that inside | | symbols. I tried like below But not successes. I am new to PL SQL. Please give some suggestions to this. Do i need a cursor or loop for do this
SELECT
regexp_substr(profile_value,'\|([^|]+)\|',1,1,NULL,1)
FROM table1
As a result of my query i am getting only WO_NO=1^
This is how I understood it.
SQL> with
2 test (col) as
3 (select '9D49|WO_NO=1^|WO no: 1;D972|WO_NO=60003^|WO no: 60003;AED1' from dual union all
4 select '1234|WO_NO=25^|WO no:25;797|WO_NO=26^' from dual
5 ),
6 temp as
7 (select regexp_substr(col, '[^|]+', 1, column_value) val,
8 column_value cv,
9 col
10 from test cross join
11 table(cast(multiset(select level from dual
12 connect by level <= regexp_count(col, '\|') + 1
13 ) as sys.odcinumberlist))
14 )
15 select listagg(val, ' or ') within group (order by cv) result
16 from temp
17 where substr(val, 1, 5) = 'WO_NO'
18 group by col ;
RESULT
--------------------------------------------------------------------------------
WO_NO=25^ or WO_NO=26^
WO_NO=1^ or WO_NO=60003^
SQL>
Splitting the string into items
Assuming (for now, stay tuned) that Table1 has a single row, you can write a regular expression and use it in a recursive query to split the string in a single select.
level denotes the level of recursion. regexp_substr lets you specify the occurrence in the string. connect by provides the exit condition: taking the next item, until there is none.
select
level as ItemNr,
regexp_substr(profile_value, '[^|]+', 1, level) as Item
from
Table1
connect by
regexp_substr(profile_value, '[^|]+', 1, level) is not null
Combining the wanted items into a new string
You could use ListAgg with a case condition, to combine only the items where level is an even number back into a single string, with the string ' or ' as a separator.
select
listagg(
case when mod(level, 2) = 0 then
regexp_substr(profile_value, '[^|]+', 1, level)
end, ' or ') as Items
from
Table1
connect by
regexp_substr(profile_value, '[^|]+', 1, level) is not null
For multiple rows
The query above doesn't work well by itself if Table1 has multiple rows. It assumes a single input value. Using yet another trick, cross apply, you can make this a subquery that does this transformation for each of the rows of your table.
The query below shows the original profile_value column, and the transformed items for each of the rows in Table1.
select
t.*, x.*
from
Table1 t
cross apply (
select
listagg(
case when mod(level, 2) = 0 then
regexp_substr(profile_value, '[^|]+', 1, level)
end, ' or ') as Items
from
dual -- just selecting a single profile_value
connect by
regexp_substr(profile_value, '[^|]+', 1, level) is not null
) x
In the end this works okay for a not to large set of data, but you're storying structured data into a string, and querying this is not very efficient.
If you need to repeat this query a lot, and the number of rows in Table1 is growing, it's probably better to store it in a more structured way. If this data is coming from an external source, you could dump it in a temporary table, and transform it using the queries of this answer, to write it to a final, better structured table (or tables) for repeated use.
