根据分号或者逗号来划分第一个有效的11位电话号码

[liao_1000]

求助：
a表里面一个字段phone用来存储电话和固话，
oracle能否根据分号或者逗号来划分第一个有效的11位电话号码，固定电话排除。

13543364231；15989213791--中文分号,只取第一个13543364231
13138712139；2886905 --中文分号，取第一个13138712139
2871132；13686737699 --中文分号，取第二个13686737699
2211389/15818911218 ---斜杠号，取第二个15818911218
2295047,13413330936 --英文逗号，取第二个13413330936
2870810，13686736353 ---中文逗号，取第二个13686736353

[阿巴库克]

给你个思路吧 先用替换函数TRANSLATE 替换分隔符为 统一字符 如 / ，然后 再进行拆分；我以前是用excel拆分的 - - 然后就可以提取了；不知道数据量有多大？phone字段确定只有两组号码吗？我遇到过乱起八糟的都有 - -

[CT_LXL]

引用 楼主 liao_1000 的回复:

with t as
 (select '13543364231；15989213791' str
    from dual
  union all
  select '13138712139；2886905' str
    from dual
  union all
  select '2871132;13686737699' str
    from dual
  union all
  select '2211389/15818911218' str
    from dual
  union all
  select '2295047,13413330936' str
    from dual
  union all
  select '2870810，13686736353' str
    from dual)
select decode(regexp_instr(str, '(;|；|/|,|，)'),
              12,
              substr(str, 1, 11),
              substr(str, regexp_instr(str, '(;|；|/|,|，)') + 1))
  from t;

[bw555]

with t as
 (select '13543364231；15989213791' str
    from dual
  union all
  select '13138712139；2886905' str
    from dual
  union all
  select '2871132;13686737699' str
    from dual
  union all
  select '2211389/15818911218' str
    from dual
  union all
  select '2295047,13413330936' str
    from dual
  union all
  select '2870810，13686736353' str
    from dual)
SELECT DECODE(LENGTH(N1),11,N1,N2) N FROM
(select regexp_substr(str,'[0-9]+',1,1) N1,regexp_substr(str,'[0-9]+',1,2) N2 from T)


N
------------------------------------------------
13543364231
13138712139
13686737699
15818911218
13413330936
13686736353

已选择6行。

[bw555]

以上语句支持任何间隔符，只要间隔符不是数字就可以

[bw555]

SQL> with t as
  2   (select '13543364231；15989213791' str
  3      from dual
  4    union all
  5    select '13138712139；2886905' str
  6      from dual
  7    union all
  8    select '2871132;13686737699' str
  9      from dual
 10    union all
 11    select '2211389/15818911218' str
 12      from dual
 13    union all
 14    select '2295047,13413330936' str
 15      from dual
 16    union all
 17    select '2870810，13686736353' str
 18      from dual)
 19  select regexp_substr(str,'[0-9]{11}',1,1) N from T;

N
------------------------------------------------
13543364231
13138712139
13686737699
15818911218
13413330936
13686736353

已选择6行。