CODEWITHSUNDEEP
sqloracle10g
anand krishna
anand krishna

Reputation: 27

splitting string based on delimiters in oracle

I have a table like this

name Description
VM1  SP L A - WINSVRS #P19  QTY 1
VM2  SPLA - VRSTD #P29-9 9 99 QTY 2 : SPLVRENT #P3 999999 9 QTY 3
VM3  SPLA - WINSVRSTD #P39-9 999 QTY 3
VM4  SPLA - WI NS V R STD #P59- 9 9 99 QTY 2 : S P LA - W IN SV RENT #P39-9999 QTY 3 :   SPLA - WIN S VR SMB # P 3 9- 999 99 QTY 5
VM6  SPLA - WINSVRSQLSERVERaaaaaaaaaSTD #P 6 9-9 9 9 9 QTY 6

and I need output like this

name Description                     ponumber
-------------------------------------------------
VM1  SP L A - WINSVRS                   P19 
VM2  SPLA - VRSTD                       P29-9 9 99                      
VM2  SPLVRENT                           P3 999999 9
VM3  SPLA - WINSVRSTD                   P39-9 999
VM4  SPLA - WI NS V R STD               P59- 9 9 99
VM4  S P LA - W IN SV RENT              P39-9999
VM4  SPL A - WIN S VR SMB               P 3 9- 999 99
VM6  SPLA - WINSVRSQLSERVERaaaaaaaaaSTD P 6 9-9 9 9 9

help me how to get the output using substring and instr string using loop

thanks and regards Anand

Upvotes: 0

Views: 3480

Answers (1)

rs.
rs.

Reputation: 27427

Try this

with CTE as
(
  select name, 
  trim(cast(v.column_value.extract('//text()') as varchar2(50))) description
  from t,
  table( xmlsequence( xmltype(
    '<descriptions><description>' 
     || replace(description, ':', '</description><description>') 
     || '</description></descriptions>'
        ).extract('//descriptions/*'))) v
 )
select 
 name,
 REGEXP_SUBSTR(description,'[^#]+') description,
 --REGEXP_SUBSTR(description,'[^#]+',1,2) phnQty,
 Trim(REGEXP_SUBSTR(REGEXP_SUBSTR(description,'[^#]+',1,2),'[^Q]+')) phn
from cte;

SQL DEMO

Upvotes: 2

Related Questions