Oracle中处理字符串的一些SQL查询技巧小结

最新推荐文章于 2024-08-13 14:24:24 发布
最新推荐文章于 2024-08-13 14:24:24 发布
阅读量2k 收藏 10
点赞数
分类专栏： 数据库 SQL 文章标签： oracle sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wzw114/article/details/120479366
版权
本文回顾了在Oracle数据库中处理字符串的技巧，包括字符串与时间、数字的转换，如to_char函数的应用；检索特定字符和位置，以及使用asciistr区分中英文；字符串加工如大小写处理、截取和替换等。
摘要由CSDN通过智能技术生成

目录

前言

 一、字符串与其他数据类型转换 

1、字符串转为时间

2、to_char将日期或数字转为字符串

 二、检索字符串

 1、区分中、英文

2、转义字符

3、检索指定字符串在源字符串的位置

4、查询某个字符串在源字符串中出现的次数

三、字符串加工为新字符串

1、大小写处理

2、去除字符串首尾的指定字符串

3、根据指定位置截取字符串

4、根据指定字符串来截取源字符串

前言

       有较长时间没有使用过Oracle数据库管理系统了，感觉有点生疏了。今天将以前使用Oracle处理字符串的一些技巧或者说是比较“方言”的部分汇总一下，以便以后再用时能较快上手。内容不算很全面，后期想起了未录入的内容再补充。

 一、字符串与其他数据类型转换 

1、字符串转为时间

SQL> select to_date('2020-10-01 15:21:20','yyyy-mm-dd hh24:mi:ss') from dual;
TO_DATE('2020-
--------------
01-10月-20

 注：比较常规，但是其他平台不一定有此函数，故作记录。

2、to_char将日期或数字转为字符串

--系统时间转字符串
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2
------------------------------
2020-09-21 16:26:47
--查询数字20为20美元
SQL> select ltrim(to_char(20,'$99,999.9999')) from dual;
LTRIM(TO_CHAR(20,'$99,999.9999
------------------------------
$20.0000
--查询数字20为本地货币即RMB
SQL> select ltrim(to_char(20,'L99,999.9999')) from dual;
LTRIM(TO_CHAR(20,'L99,999.9999
------------------------------
￥20.0000
--查询数字20为国际货币
SQL> select ltrim(to_char(20,'C99,999.9999')) from dual;
LTRIM(TO_CHAR(20,'C99,999.9999
------------------------------
CNY20.0000
--查询数字9998为8位字符串（不足以0补齐）并拼接前缀
SQL> select 'BH'||ltrim(to_char(9999-1,'00000000')) from dual;
'BH'||LTRIM(TO_CHAR(9999-1,'00
------------------------------
BH00009998

注：其他平台不一定有此函数，to_char的可以实现的业务需求还是比较多的。合理使用在一定程度上可以减少应用程序的工作量。

 二、检索字符串

 1、区分中、英文

SQL> select asciistr('穷') from dual;
ASCIISTR('穷')
--------------
\7A77

SQL> select asciistr('pool') from dual;
ASCIISTR('POOL')
----------------
pool

asciistr函数区分中、英文；中文的返回值包含'\'

2、转义字符

--用于查询的视图
SQL> select '20200921_1' as unid from dual union select '1234567890'as unid from dual;
UNID
----------
1234567890
20200921_1

SQL> select * from (select '20200921_1' as unid from dual union select '1234567890'as unid from dual) where unid like '%\_%' escape '\';
UNID
----------
20200921_1

注：查询的字符串含有通配符，使用like语句需要自定义转义字符。（有些计算机语言的转义字符不需要自定义的）

3、检索指定字符串在源字符串的位置

SQL> --检索"\"在text首次出现的位置，若不存在则返回0
SQL> select instr(text,'\') from (select 'E:\nginx-1.19.4\conf\cert\mycert.key' as text from dual);
INSTR(TEXT,'\')
---------------
              3
SQL> select instr(text,'三') from (select 'E:\nginx-1.19.4\conf\cert\mycert.key' as text from dual);
 INSTR(TEXT,'三')
----------------
               0
SQL> --检索"\"在text从头数第二次出现的位置
SQL> select instr(text,'\',1,2) from (select 'E:\nginx-1.19.4\conf\cert\mycert.key' as text from dual);
INSTR(TEXT,'\',1,2)
-------------------
                 16
SQL> --检索"\"在text从第4位数第一次出现的位置
SQL> select instr(text,'\',4,1) from (select 'E:\nginx-1.19.4\conf\cert\mycert.key' as text from dual);
INSTR(TEXT,'\',4,1)
-------------------
                 16
SQL> --检索"\"在text从第4位数第二次出现的位置
SQL> select instr(text,'\',4,2) from (select 'E:\nginx-1.19.4\conf\cert\mycert.key' as text from dual);
INSTR(TEXT,'\',4,2)
-------------------
                 21
SQL> --检索"\"在text从倒数第1位第一次出现的位置
SQL> select instr(text,'\',-1,1) from (select 'E:\nginx-1.19.4\conf\cert\mycert.key' as text from dual);
INSTR(TEXT,'\',-1,1)
--------------------
                  26
SQL> --检索"\"在text从倒数第1位第三次出现的位置
SQL> select instr(text,'\',-1,3) from (select 'E:\nginx-1.19.4\conf\cert\mycert.key' as text from dual);
INSTR(TEXT,'\',-1,3)
--------------------
                  16

注：instr在其他平台不一定有，此函数在查询疑似异常字符串时有用武之地。instr在一定程度上还有代替like语句，实现模糊查询，且不存在通配符。

--查询字符串含有通配符，可使用instr函数，从而省去定义转义字符的工作。
SQL> select * from (select '20200921_1' as unid from dual union select '1234567890'as unid from dual) where instr(unid,'_')>0;
UNID
----------
20200921_1

4、查询某个字符串在源字符串中出现的次数

SQL> select length(translate(text,','||text,'&')) as 逗号出现次数 from (select '晓明,晓华,晓晓' as text from dual);
      逗号出现次数
------------
           2
SQL> select length(translate(text,'晓'||text,'&')) as 晓出现次数 from (select '晓明,晓华,晓晓' as text from dual);
     晓出现次数
----------
         4
SQL> select length(text)-length(replace(text,'晓','')) as 晓出现次数 from (select '晓明,晓华,晓晓' as text from dual);
     晓出现次数
----------
         4

注：一个translate表达式可以替换多个需要替换的字符串，一个replace表达式只能替换一个需要替换的字符串。

translate(text,','||text,'&') 意思是讲text中的逗号替换为&，除逗号以外因没有指定替换字符，而被系统替换为空。保留下来的字符串只有源字符串中的逗号，计算字符长度就得到了该字符出现的次数。深入理解需要查看translate用法。

使用replace也可以实现同样的功效，表达式较好理解。

三、字符串加工为新字符串

1、大小写处理

SQL> select upper('helloworld!') from dual;

UPPER('HELL
-----------
HELLOWORLD!

SQL> select lower('HELLOWORLD!') from dual;

LOWER('HELL
-----------
helloworld!

SQL> select initcap('HELLO WORLD!') from dual;

INITCAP('HEL
------------
Hello World!

 注：其他平台的函数名称可能不同，故记录一下。

2、去除字符串首尾的指定字符串

SQL> select trim(text) as 去除字符两边的空格 from(select ' 时间 空间 属性 '  as text from dual);
去除字符两边的空格
------------------
时间 空间 属性
SQL> select ltrim(text) as 去除字符前边的空格 from(select ' 时间 空间 属性 '  as text from dual);
去除字符前边的空格
------------------
时间 空间 属性
SQL> select rtrim(text) as 去除字符后边的空格 from(select ' 时间 空间 属性 '  as text from dual);
去除字符后边的空格
------------------
 时间 空间 属性
SQL> select trim(leading '《' from text) as 去除字符前边的书名号 from(select '《时间 空间 属性》'  as text from dual);
去除字符前边的书名号
--------------------
时间 空间 属性》
SQL> select trim(trailing '》' from text) as 去除字符后边的书名号 from(select '《时间 空间 属性》'  as text from dual);
去除字符后边的书名号
--------------------
《时间 空间 属性
SQL> select trim(both '"' from text) as 去除字符两边的引号 from(select '"时间 空间 属性"'  as text from dual);
去除字符两边的引号
------------------
时间 空间 属性

 注：比较好理解，其他平台的写法可能不一样，个人用trim去除指定字符用的较少，用来去除空格较多一点，故记录一下。

3、根据指定位置截取字符串

SQL> --从前边数第一个字符的位置向后取三个字符
SQL> select substr(text,1,3) as str from (select '安徽省合肥市蜀山区长江西路' as text from dual);
STR
------
安徽省
SQL> --从前边数第四个字符的位置向后取三个字符
SQL> select substr(text,4,3) as str from (select '安徽省合肥市蜀山区长江西路' as text from dual);
STR
------
合肥市
SQL> --从后面数第七个字符的位置向后取三个字符
SQL> select substr(text,-7,3) as str from (select '安徽省合肥市蜀山区长江西路' as text from dual);
STR
------
蜀山区
SQL> --从后面数第四个字符的位置向后取四个字符
SQL> select substr(text,-4,4) as str from (select '安徽省合肥市蜀山区长江西路' as text from dual);
STR
--------
长江西路
SQL> --从前边数第四个字符的位置向后取全部字符
SQL> select substr(text,4)  as str from (select '安徽省合肥市蜀山区长江西路' as text from dual);
STR
--------------------
合肥市蜀山区长江西路
SQL> --从后面数第四个字符的位置向后取全部字符
SQL> select substr(text,-4) as str from (select '安徽省合肥市蜀山区长江西路' as text from dual);
STR
--------
长江西路

注：指定从左开始的位置的截取用的较多，指定从右开始的位置的截取用也有用武之地。不管指定从左开始的位置，还是从右开始的位置，此函数截取字符串时都是从左往右截取，没有从右向左截取的语法。

4、根据指定字符串来截取源字符串

SQL> --指定-为分隔符，截取第一个分隔符和第二个分隔符之间的内容
SQL> select substr(text,instr(text,'-',1,1)+1,instr(text,'-',1,2)-1-instr(text,'-',1,1)) from(select '2020级-汉语言文学-张伟' as text from dual);
SUBSTR(TEXT,INSTR(TEXT,'-',1,1
------------------------------
汉语言文学

这种截取方式将instr的检索结果作为substr的入参来处理，实现了根据字符串来“分割”两个“分割符号”间的内容。不是很方便阅读。但是这种方法的实现原理很好理解，相对灵活。故记录一下。（印象中不止这一种截取方法）

以下是PL/SQL程序实现的根据指定字符串截取的源字符串的一个示意代码。

SQL> set serveroutput  on;
SQL> --指定分隔符，用分隔符号将文本拆开
SQL> declare
  2  v_str varchar2(100);--源字符串
  3  v_indexof varchar2(1);--分隔符号
  4  v_substr varchar2(100);--每一次分割的结果
  5  v_count Integer;--指定字符在源字符串中出现次数
  6  v_begin Integer;--开始位置
  7  v_end Integer;--结束位置
  8  begin
  9    v_indexof := '-';
 10    v_str:='2020级-汉语言文学-张伟';
 11    --求指定字符在源字符串中出现次数
 12    v_count := length(translate(v_str,v_indexof||v_str,'&'));
 13    v_begin := 0;
 14    for i in 1..v_count+1 loop
 15
 16      if i<=v_count then
 17        --求指定字符在源字符串中第i次出现的次数,赋值给v_end
 18        v_end := instr(v_str,v_indexof,1,i);
 19      elsif  i=v_count+1 then
 20        --求源字符串长度+1,赋值给v_end.目的是截取最后一个分隔符号后面的内容
 21        v_end := length(v_str)+1;
 22      end if;
 23      --v_begin+1为开始截取位置，v_end-1-v_begin为截取的长度
 24      v_substr := substr(v_str,v_begin+1,v_end-1-v_begin)
 25      dbms_output.put_line(v_substr);
 26      v_begin :=v_end;
 27    end loop;
 28  end;
 29  /
2020级
汉语言文学
张伟

PL/SQL 过程已成功完成。

我一时想不起
关注
专栏目录
Oracle中关于字符串操作的SQL语句实例
我是一名程序猿
02-13 844
1. 连接两个字符串，concat只能连接两个字符串哦 select concat('ABC','DEF') from dual; select 'ABC' || 'DEF' || 'GH' from dual; select e.ename || ',' || e.job from emp e; 2. 把每个单词的首字母都变成大写，函数initcap select initcap('aaa...
oracle中执行字符串sql语句,Oracle SQL 字符串操作
weixin_35028513的博客
04-02 2093
这些函数全都接收的是字符族类型的参数(CHR除外)并且返回字符值.除了特别说明的之外,这些函数大部分返回VARCHAR2类型的数值.字符函数的返回类型所受的限制和基本数据库类型所受的限制是相同的。字符型变量存储的最大值：VARCHAR2数值被限制为2000字符(ORACLE8中为4000字符)CHAR数值被限制为255字符(在ORACLE8中是2000)long类型为2GBClob类型为4GB1、...
Oracle动态SQL使用
最新发布
qq_53560046的博客
08-13 536
动态SQL
Oracle SQL 字符串操作
lizhengyu617的专栏
11-30 664
这些函数全都接收的是字符族类型的参数(CHR除外)并且返回字符值.   除了特别说明的之外,这些函数大部分返回VARCHAR2类型的数值.   字符函数的返回类型所受的限制和基本数据库类型所受的限制是相同的。   字符型变量存储的最大值：   VARCHAR2数值被限制为2000字符(ORACLE 8中为4000字符)   CHAR数值被限制为255字符(在ORACL
oracle中执行字符串sql语句,shell脚本分割字符串oracle执行sql语句
weixin_33543395的博客
04-02 393
Dimh0AsInteger'ho,mo,so为初始时间Dimm0AsIntegerDims0AsIntegerPrivateSubCommand5_Click()'用鼠标单击"上一题"IfVal(Label1.Caption)>1ThenData1.Recordset.MovePreviousElseTitle="提示"msg2$="这是第一题!"MsgBoxmsg2$,0,TitleEn...
orcale如何执行字符串SQL以及几种应用
liuhongJAVAEn的专栏
07-04 1026
declare   s_sql varchar(1000):='update jobs set job_title=''President'' where job_id=''AD_PRES''';  retCnt number;  job_id varchar
ADO.NET 连接数据库字符串小结(Oracle、SqlServer、Access、ODBC)
01-02
ADO.NET 连接到 SQL Server SQL Server .NET Framework 数据提供程序支持类似于 OLE DB (ADO) 连接字符串格式的连接字符串格式。 代码如下:using (SqlConnection connection = new SqlConnection(connectionString))...
sqlserver和oracle中对datetime进行条件查询的一点区别小结
09-11
在SQL Server和Oracle数据库中，对DateTime类型进行条件查询时，两者存在一些差异，这主要体现在日期范围的处理以及日期格式上。对于一个系统需要同时支持这两种数据库的情况，需要特别注意这些不同点来编写兼容的...
Oracle中的字符串类型及相关函数详解
anxpp的博客
06-22 1万+
1、概述 本文介绍String类型及相关的函数，基于当前最新的Oracle 12c 为基础作介绍。 下文将字符串简称为串。 Oracle函数的工作方式有两种： 1、根据旧的对象创建新的对象——他们对原来的信息进行修改，如改变字母的大小写。 2、告诉用户有关的信息，如一个单词或句子中有几个字符。 后续会更新另外两种处理文本的方式：Oracle中的正则表达式 和 Oracle Text工具，等文章编辑完成，会在此处添加链接。 Oracle中主要有两种字符
oracle中sql函数小结
12-04
### Oracle SQL 函数小结 Oracle数据库作为一款广泛使用的数据库管理系统，在进行数据处理与分析时提供了丰富的内置函数，这些函数可以极大地简化SQL查询的编写工作，并提高查询效率。本文将根据给定的内容对Oracle...
Oracle SQL 执行
u011868279的博客
10-02 4394
Oracle SQL 执行
Oracle_Sql_中常用字符串处理函数
06-13
Oracle_Sql_中常用字符串处理函数
oracle判断是否包含字符串的方法
热门推荐
jumtre的专栏
07-21 11万+
首先想到的就是contains，contains用法如下：
Oracle函数 SQL
偷得浮生半日闲
01-11 1858
Oracle函数是Oracle数据库提供给用户类似方法的API 抽取常用的函数进行使用
ORACLE小知识点
小码哥
02-03 314
1、将字符串转换成SQL语句用 "IMMEDIATE" eg: EXECUTE IMMEDIATE "UPDATE TEST SET ID = 1"; 2、WHENEVER SQLERROR   语法：   WHENEVER SQLERROR   {EXIT [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable]   [COMMIT
Oracle字符串处理之包含
mosaic&Bernie
02-28 4445
在PL/SQL中经常会用碰到字符串的处理问题，比如截取、拼接、包含、大小写处理等等。今天呢，就对包含做一简单的说明： 1、本人建议使用的是INSTR()函数，理由呢，是处理速度快，限制条件少；示例如下： SELECT * FROM students WHERE INSTR(name,'康')>0 2、其次，使用比较笨但好多人都在用的方法LIKE，为什么说比较笨呢，因为它处理效率低嘛 S
oracle查找数据库字符串,oracle操作字符串：拼凑、替换、截取、查找
weixin_39855658的博客
04-12 401
1、拼接字符串1)可以使用“||”来拼接字符串select '你好'||'世界！' as str from dual ;2)通过concat()函数实现select concat('你好', '我的世界') as str from dual ;注：oracle的concat函数只支持两个参数的方法，即只能拼接两个参数，如要拼接多个参数则嵌套使用concat可实现，如：select concat(c...
oracle里如何执行sql串语句
零点烛光的专栏
04-06 1632
在ms sqlserver里，我们使用exec sql来执行sql语句在oracle里多数需要使用变量来执行，这有待研究一下。1,select into的执行   v_sql := select count(*) from ( || Psqlcount || );   execute immediate v_sql into v_prcount;   Prcount := v_
Oracle SQL查询深入解析
本资料详细介绍了Oracle SQL中的SELECT查询语句，涵盖了一系列核心概念和技术。 1. SELECT查询基本语法 SELECT语句的基本结构是`SELECT <列名> FROM <表名>`，用于从指定的表中检索数据。例如，`SELECT * FROM s_...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

我一时想不起

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值