如何解析oracle Query中的文本

Question

When I am running my query on Oracle I receive this result on same column :

text1 - text2 - text3 - text4

( there are many columns )

I should have count from each text

thanks for your help

query:

     select ca.title
     from table_case ca,
    table_contract x, table_site_part sp, mtm_site_part24_contract5 mtm,
    table_queue q,
    table_user u_og,
    table_employee empl,
    table_site office,
    table_site s,
    table_address addr,
    table_contact_role co_r,
    table_contact co,
    table_gbst_elm gb,
    table_x_tt_ticket tt,
    table_case vc
    where ca.case_originator2user = u_og.objid
    and x.objid=mtm.contract2dir_sitepart
    and ca.case_currq2queue = q.objid
    and ca.CASESTS2GBST_ELM=gb.objid
    and u_og.objid = empl.employee2user
    and empl.SUPP_PERSON_OFF2SITE = office.objid
    and ca.case_reporter2site = s.objid
    and s.cust_primaddr2address = addr.objid
    and s.objid = co_r.contact_role2site
    and co_r.contact_role2contact = co.objid
    and sp.objid=mtm.dir_sitepart2contract
    and sp.all_site_part2site=s.objid
    and co_r.s_role_name = 'DEFAULT'
    and q.s_title = upper('OP_AD_WIN_TECH') 
    and ca.objid = tt.X_TT_TICKET2CASE (+)
    and tt.X_HANDLING_UNIT IS NULL
    and ca.CASE_VICTIM2CASE=vc.OBJID(+)
    and x.type not in ('UMTS','GSM','???/???','.','Time and Materials') 

result:

      63CAGDIRO - VoIP/VDSL - No Service - All
      63CAGDIRO - VoIP/VDSL - No Service - All
      79WIIHAHG - VDSL - Quality Internet - Internet
      79WIIHAHG - VDSL - Quality Internet - Internet
      71GROGRO - VDSL - tv box error code general arg fail - TV
      71GROGRO - VDSL - tv box error code general arg fail - TV
      73LATLWHS - VDSL - No Service All - All services
      73LATLWHS - VDSL - No Service All - All services 

Answer 1

If you are looking to 'convert' one text in multiple columns you could do something like :

with 
text as (
    select '63CAGDIRO - VoIP/VDSL - No Service - All'  as txt from dual union all
    select '63CAGDIRO - VoIP/VDSL - No Service - All' from dual union all
    select '79WIIHAHG - VDSL - Quality Internet - Internet' from dual union all
    select '79WIIHAHG - VDSL - Quality Internet - Internet' from dual union all
    select '71GROGRO - VDSL - tv box error code general arg fail - TV' from dual union all
    select '71GROGRO - VDSL - tv box error code general arg fail - TV' from dual union all
    select '73LATLWHS - VDSL - No Service All - All services' from dual union all
    select '73LATLWHS - VDSL - No Service All - All services ' from dual),
pos as (
    select txt, instr(txt,'-',1,1) as p1, instr(txt,'-',1,2) as p2, 
                instr(txt,'-',1,3) as p3 from text
    )
select
     substr(t.txt, 1, p1 -1 )               col_name_1,
     substr(t.txt, p1 + 1, p2 - p1 - 2 )    col_name_2,
     substr(t.txt, p2 + 1, p3 - p2 - 2 )    col_name_3,
     substr(t.txt, p3 + 1, length(t.txt) )  col_name_4
  from text t , pos p 
  where t.txt = p.txt;

Result will be:

COL_NAME_1      COL_NAME_2      COL_NAME_3                          COL_NAME_4    
63CAGDIRO        VoIP/VDSL       No Service                          All           
63CAGDIRO        VoIP/VDSL       No Service                          All           
63CAGDIRO        VoIP/VDSL       No Service                          All           
63CAGDIRO        VoIP/VDSL       No Service                          All           
79WIIHAHG        VDSL            Quality Internet                    Internet      
79WIIHAHG        VDSL            Quality Internet                    Internet      
79WIIHAHG        VDSL            Quality Internet                    Internet      
79WIIHAHG        VDSL            Quality Internet                    Internet      
71GROGRO         VDSL            tv box error code general arg fail  TV            
71GROGRO         VDSL            tv box error code general arg fail  TV            
71GROGRO         VDSL            tv box error code general arg fail  TV            
71GROGRO         VDSL            tv box error code general arg fail  TV            
73LATLWHS        VDSL            No Service All                      All services  
73LATLWHS        VDSL            No Service All                      All services 

I notice a pattern, it seems to me that the column are delimited by '-'.