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

hresh 553 0

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

发表评论 取消回复
表情 图片 链接 代码

分享