Oracle语句判断字符串是否为数字及translate函数解析

判断字符串是否全为数字

最近在工作中遇到这样一个问题，判断字符串中是否全为数字，Oracle 数据库的操作方法有以下几种：

1、使用 trim+translate 函数

select * from dual where trim(translate(column,'0123456789',' ')) is NULL;

这里要注意的是：translate 函数的第三个参数是一个空格，不是'', 因为translate的第三个参数如果为空的话，那么永远返回'',这样的就不能达到过滤纯数字的目的。这样把所有的数字都转化为空格，如果全部是由数构成，那么一旦trim后自然是空，实现了上述目标。当然如果想排除空项的话，可以这样写：

select * from dual where trim(translate(nvl(column,'x'),'0123456789',' ')) is NULL;--x 表示任何'0-9'以外的字符。

NVL函数是一个空值转换函数

NVL(exp1，exp2)函数，如果exp1为空值，则返回exp2；否则返回exp1。

注意：当 column 的值为空格时，也会被筛选到，所以比较好的方法是使用 replace+translate 函数。

2、使用 replace+translate 函数

select * from dual where replace(translate(column,'0123456789','0'),'0','') is NULL;

3、使用 regexp_like 函数

select * from dual where regexp_like(column,'^[0-9]+[0-9]$')

这里需要注意的是：regexp_like 函数不是在所有的 Oracle 版本中都能使用。regexp_like 是 Oracle 支持正则表达式的函数中的一个，共有四个函数：regexp_like ，regexp_replace，regexp_instr，regexp_substr。

translate 函数讲解

上述方法比较关键的是 translate 函数，对于该函数查看相关讲解如下：

translate(expr, from_strimg, to_string)

translate 函数返回 expr，其中 from_string 中的每个字符都被 to_string 中的相应字符替换。若 expr 中某个字符未出现在 from_string 中，则该字符不会被替换。如果 expr 是一个字符串，那么你必须把它放在单引号中。 from_string 参数值可以包含比 to_string 更多的字符。在这种情况下，from_string 末尾的多余字符在 to_string 中没有对应的字符。如果这些额外的字符出现在 expr 中，那么它们将从 expr 返回值中移除。

to_string 参数值为空字符串时，expr 返回值中删除 from_string 中的所有字符。Oracle 数据库将空字符串解释为空，如果此函数具有空参数，则返回 null。

translate 提供了与 replace 函数相关的功能。 replace 允许用一个字符串替换另一个字符串，以及删除字符串。 translate 允许在一个操作中进行多个单字符，一对一的替换。

实际案例：

1、基本用法，字符对应替换

select translate('1234567','123' ,'abc') from dual ;--1替换为a,2替换为b,3替换为c
-------
abc4567

2、如果 to_string 没有对应字符则删除额外的字符

select translate('1234567','123' ,'ab') from dual；--‘1234567’中的‘3’被删掉;
-----
ab4567

3、如果 to_string 对应字符过多不影响

select translate('1234567','123' ,'abccd') from dual；   ---‘123’对应‘abc’

4、如果 to_string 为空字符，则直接返回 NULL

select translate('1234567','123' ,'') from dual；
----
null

5、如果想删除 expr 中某些字符，除了 from_strimg 拥有这些字符外，还应传入一个不相关字符，同时 to_string 中也需要传入该字符

SELECT  translate('0123456789','@123' ,'@') FROM dual
----
0456789

其实原理和 3 一样，'@123'比'@'长，额外的'123'字符在 expr 中出现，因此去除掉 expr 中的’123‘。

需要注意的是，如果更改'@'字符在 from_strimg 中的位置，结果就不一样了。

SELECT  translate('0123456789','123@' ,'@') FROM dual
----
0@456789

原因在于'123@'中的'1'字符和'@'对应，则'23@'就属于额外的字符，从 expr 返回值中删除'23'字符。

6、如果 from_strimg 相同字符对应 to_string 多个字符，按去第一个；

SELECT  translate('01233456789','1233' ,'abcd') FROM dual
----
0abcc456789

7、如果想保留某些特定字符筛选掉其他的

比如筛掉汉字保留数字

先删除数字

SELECT  translate('我5喜2欢1你','@0123456789' ,'@') FROM dual
-----
我喜欢你

再用筛选出的汉字去筛选原来的语句留下数字，

SELECT  translate('我5喜2欢1你','@'||translate('我5喜2欢1你','@0123456789' ,'@') ,'@') FROM dual
----
521

8、判断 from_strimg 中包含的字符是否一致（排序可以不同）

SELECT  1 FROM dual where translate('abcdefgh','bcd' ,'111111')=translate('abcdefgh','cbd' ,'111111')
----
1
----反之
SELECT  1 FROM dual where translate('abcdefgh','bdd' ,'111111')=translate('abcdefgh','cbd' ,'111111')
----
null

9、同 replace 函数比较

select translate('itmyhome#163%com', '#%', '@.') from dual;
select replace('itmyhome#163%com', '#%', '@.') from dual;

---------
itmyhome@163.com
itmyhome#163%com

本文作者为hresh，转载请注明。

SQL