时间戳

首页 > 解决方案 > How to select rows with only Numeric Characters in Oracle SQL

sql - How to select rows with only Numeric Characters in Oracle SQL

问题描述

I would like to keep rows only with Numeric Character i.e. 0-9. My source data can have any type of character e.g. 2,%,( .

Input (postcode)
3453gds sdg3
454232
sdg(*d^
452

Expected Output (postcode)
454232
452

I have tried using WHERE REGEXP_LIKE(postcode, '^[[:digit:]]+$'); however in my version of Oracle I get an error saying

function regexp_like(character varying, "unknown") does not exist

标签： sqloracle

解决方案

ForOracle 10或更高版本，您可以使用regexp函数。在早期版本中，翻译功能将帮助您：

SELECT  postcode
FROM    table_name
WHERE   length(translate(postcode,'0123456789','1')) is null
AND     postcode IS NOT NULL;

或者

SELECT translate(postcode, '0123456789' || translate(postcode,'x123456789','x'),'0123456789') nums
   FROM table_name  ;

上面的答案也适用于我

SELECT translate('1234bsdfs3@23##PU', '0123456789' || translate('1234bsdfs3@23##PU','x123456789','x'),'0123456789') nums
   FROM dual  ;

Nums:
1234323

推荐阅读