Oracle SQL查询以排除包含非数字字符的行 [英] Oracle SQL query to exclude rows containing non-numeric characters
本文介绍了Oracle SQL查询以排除包含非数字字符的行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下运行良好的oracle查询
I have the following oracle query which runs fine
SELECT c.customercode, s.sales_id
FROM customers c
LEFT JOIN sales s ON c.customercode = s.sales_id
WHERE c.address IS NOT NULL
并返回类似于以下内容的结果:
and it returns results looking something like this:
customercode sales_id
12345678 456
34534534 678
23423423 X123
我想做的是排除sales_id包含除数字字符以外的任何内容的行.因此,以上结果将不包括第三行.
What I would like to do is exclude rows where the sales_id contains anything other than numeric characters. So the above results would not include the 3rd row.
推荐答案
您可以使用正则表达式.在这种情况下,regexp_like( sales_id, '^[[:digit:]]*$' )
You can use regular expressions. In this case, regexp_like( sales_id, '^[[:digit:]]*$' )
SQL> with x as (select 12345678 code, '456' sales_id from dual union all
2 select 34534534, '678' from dual union all
3 select 23423423, 'X123' from dual)
4 select *
5 from x
6 where regexp_like( sales_id, '^[[:digit:]]*$' );
CODE SALE
---------- ----
12345678 456
34534534 678
这篇关于Oracle SQL查询以排除包含非数字字符的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文