split string in oracle query

I am trying to fetch phone numbers from my Oracle database table. The phone numbers may be separated with comma or "/". Now I need to split those entries which have a "/" or comma and fetch the f开发者_JAVA技巧irst part.

Follow this approach,

with t as (
  select 'Test 1' name from dual
  union
  select 'Test 2, extra 3' from dual
  union
  select 'Test 3/ extra 3' from dual
  union
  select ',extra 4' from dual
)
select
  name,
  regexp_instr(name, '[/,]') pos,
  case
    when regexp_instr(name, '[/,]') = 0 then name
    else substr(name, 1, regexp_instr(name, '[/,]')-1) 
  end first_part
from
  t
order by first_part
;

Lookup substr and instr functions or solve the puzzle using regexp.

I added a table test with one column phone_num. And added rows similar to your description.

select *
from test;

PHONE_NUM
------------------------------
0123456789
0123456789/1234
0123456789,1234

3 rows selected.


select 
 case
  when instr(phone_num, '/') > 0 then substr(phone_num, 0, instr(phone_num, '/')-1)
  when instr(phone_num, ',') > 0 then substr(phone_num, 0, instr(phone_num, ',')-1)
  else phone_num
 end phone_num
from test


PHONE_NUM
------------------------------
0123456789
0123456789
0123456789

3 rows selected.

This generally works. Although it will fail if you have rows with commas and slashes.