hao同学的技术博客

  • 首页
  • Java
    • Java
    • JVM教程
    • Java面试
    • Java并发入门
    • Java并发进阶
  • 项目
    • 从零打造项目
  • Python
    • Python
    • Python爬虫
    • 算法
  • Java框架
    • Spring
    • SpringBoot
  • 前端
    • Angular
  • 其他
    • Linux
    • SQL
  • 随笔
分享技术,记录人生
一个痴迷于技术的厨艺爱好者
  1. 首页
  2. SQL
  3. 正文

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

2022年5月25日 154点热度 0人点赞 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

本作品采用 知识共享署名-非商业性使用 4.0 国际许可协议 进行许可
标签: SQL
最后更新:2022年5月25日

hresh

这是一个专注于IT技术学习交流的个人技术博客网站,包括Java学习、Python爬虫、Web开发实践等领域,深耕Java领域,内容涵盖Java基础、Java并发编程、Java虚拟机、Java面试等核心知识点。

点赞
< 上一篇
下一篇 >

文章评论

取消回复

hresh

这是一个专注于IT技术学习交流的个人技术博客网站,包括Java学习、Python爬虫、Web开发实践等领域,深耕Java领域,内容涵盖Java基础、Java并发编程、Java虚拟机、Java面试等核心知识点。

文章目录
  • 判断字符串是否全为数字
  • translate 函数讲解
最新 热点 随机
最新 热点 随机
后端必知:遵循Google Java规范并引入checkstyle检查 Spring Security结合Redis实现缓存功能 Spring Security结合JWT实现认证与授权 Spring Security自定义认证逻辑实现图片验证码登录 Spring Security进阶学习 Spring Security入门学习
Centos7.7安装及配置教程 你知道Lombok是如何工作的吗 用Python打扑克牌——炸金花 Python已知RSA模数和指数,生成公钥进行加密 Java并发编程入门学习之线程与锁 Happens-Before规则详解

COPYRIGHT © 2022 hao同学的技术博客. ALL RIGHTS RESERVED.

Theme Kratos Made By Seaton Jiang

鄂ICP备2022007381号

鄂公网安备 42010302002449号