怎样SqL语句来检索出某列中包含电话号码的数据?谢谢
本帖最后由 arlgil1 于 2013-08-08 06:06:40 编辑
比如某表中有列cont:
id cont
1 tel:13933392233
2 asdf;kj
3 x:010-38392231
4 I have a wach
5 I can give you my number:18922363298.and you?
6 your number is 021 3821234234?
7 how are you?
8 and and ... ..hah ha
9 your phone is (86)891-2828228?ok?
10 your dianing room number is 211 or 232?
通过什么语句,能够把cont中涉及电话号码的数据检索出来呢??
即检索结果为:
1 tel:13933392233
3 x:010-38392231
5 I can give you my number:18922363298.and you?
6 your number is 021 3821234234?
9 your phone is (86)891-2828228?ok?
------解决方案--------------------where regexp_like(cont,'正则表达式')
------解决方案--------------------大致的正则过滤 如果要具体的 可以网上搜索 电话正则 很多的 需求不同 写法也不同
with t1 as
(
select 1 id,'tel:13889233453' cont from dual union all
select 2 id,'x:010-38392231' from dual union all
select 3 id,'I have a wach' from dual union all
select 4 id,'ph:010-33884499' from dual union all
select 5 id,'your number is 021-3821234234?' from dual
)
select id,cont
from t1
where regexp_like(cont,'(\d{3}-)?\d{8}
------解决方案--------------------
(\d{4}-)(\d{7})')
or regexp_like(cont,'^1[3
------解决方案--------------------
4
------解决方案--------------------
5
------解决方案--------------------