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 first part.
Add a comment
|
3 Answers
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
;
Comments
Lookup substr and instr functions or solve the puzzle using regexp.
Comments
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.
