oracle oracle查询中的拆分字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4085855/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
split string in oracle query
提问by rose
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.
我正在尝试从我的 Oracle 数据库表中获取电话号码。电话号码可以用逗号或“/”分隔。现在我需要拆分那些带有“/”或逗号的条目并获取第一部分。
回答by Janek Bogucki
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
;
回答by Rene
Lookup substr and instr functions or solve the puzzle using regexp.
查找 substr 和 instr 函数或使用正则表达式解决难题。
回答by gnuchu
I added a table test with one column phone_num. And added rows similar to your description.
我添加了一个带有一列 phone_num 的表测试。并添加了与您的描述类似的行。
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.
这通常有效。尽管如果您有带有逗号和斜杠的行，它会失败。
