本文回顾了在Oracle数据库中处理字符串的技巧，包括字符串与时间、数字的转换，如to_char函数的应用；检索特定字符和位置，以及使用asciistr区分中英文；字符串加工如大小写处理、截取和替换等。
目录
前言
有较长时间没有使用过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')
----------------
poolasciistr函数区分中、英文；中文的返回值包含'\'
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_14、查询某个字符串在源字符串中出现的次数
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 过程已成功完成。
扫一扫
828
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
