oralce函数substr和mysql函数substring_index初记
oralce函数substr和mysql函数substring_index小记 ???? 最近偶尔在mysql数据库中看到了substring_index函数，先简单介绍下mysql的substring_index函数。??? substring_index(str,delim,count) 返回字符串str中在第 count 个出现的分隔符 delim之前的子串,和ja
oralce函数substr和mysql函数substring_index小记???? 最近偶尔在mysql数据库中看到了substring_index函数，先简单介绍下mysql的substring_index函数。???
substring_index(str,delim,count) 返回字符串str中在第 count 个出现的分隔符 delim之前的子串,和java中的str.substring函数类似。 如果count是正数时候，substring_index是从左到右的顺序检查分隔符delim所在的位置。 如果count是负数时候，substring_index是从右到左的顺序检查分隔符delim所在的位置，返回的是从字符串str左边下标1开始的下标位置。 特殊点如果count等于0，那么，不管delim是否在str中存在,你得到的将是一个空白字符串。如下所示
???
select substring_index('www.baidu.com', '.', 0);
select substring_index('www.baidu.com', '-', 0);??? 上面说的很抽象，来个简单的例子。
???
select substring_index('www.baidu.com', '.', 2);?? 2为正数，.在str中存在,返回的结果是从下标1到第2次.出现的位置之间substr(1,10-1)的子串。所以结果是'www.baidu'。
??
select substring_index('www.baidu.com', '.', -2);??? -2为负数，.在str中存在,.在str中搜索顺序是从右到左第2次出现的位置是4，用于是负数返回的是从最后到4+1的子串，也就是substr(str,4+1)，所以结果是baidu.com。
??? 可以看到substring_index返回的子串是不包含分隔符 delim在str中第count次匹配的位置的,如果分隔符delim在str中出现的位置小于count次会发生什么呢？来测试下：
??
select substring_index('www.baidu.com', '.', 3);
select substring_index('www.baidu.com', '.', -3);?? 运行之后可以看到返回的结果都是'www.baidu.com'。再来测试delim在str中不存在的情况：
??
select substring_index('www.baidu.com', '-', -1);
select substring_index('www.baidu.com', '-',1);??? 运行之后可以看到返回的结果都是'www.baidu.com',从上面的测试可以知道分隔符delim在str中不存在或者出现次数小于指定的count时将返回str本身。上面只是测试了英文数据，中文数据测试也是一样的，测试如下：
??
select substring_index('我是中文_测试_数据','_',2) from dual;--我是中文_测试
select substring_index('我是中文_测试_数据','_',-2) from dual;--测试_数据
select substring_index('我是中文_测试_数据','_',0) from dual;--空白字符串
select substring_index('我是中文_测试_数据','_',3) from dual;--我是中文_测试_数据??? 中英文混合的情况我就不测试了。
??? mysql有substring_index函数，oracle是否也要类似的函数呢？
我所知道的oracle中常见的只有substr和mysql的substring_index函数类似，有网友知道oralce其他函数的，麻烦留言告知。mysql中也有substr,关于两者的区别，后面我会简述。
??? 先简单的介绍下oracle的substr函数和instr函数，后面我会用这2个函数模拟mysql的substring_index函数
???
instr( string1, string2 [, start_position [, nth_appearance ] ] ) string1 源字符串 string2 目标字符串. start_position 从string1 的哪个位置开始查找。默认为1. 字符串索引从1开始。为正，从左到右开始检索，为负，从右到左检索。 nth_appearance 代表要查找第几次出现的string2. 此参数可选，默认为 1.不能为负数 返回要查找的字符串string2在源字符串string1中的符合条件的开始索引
???
substr(string,start_position,length) string 源字符串,即被截取的字符串. start_position 字符截取的开始位置.start_position大于0时,从左边算起,小于0时,从右边查起 length 截取字符的个数.默认截取到最后一位.
??? 先来个测试：
???
select instr('www.baidu.com', '.',-1,2) from dual??? .在str中存在,-1说明检索是从最后一位开始,2说明出现2次,返回的结果是4
??
select instr('www.baidu.com', '.',1,2) from dual??? .在str中存在,1说明按从左到右的顺序，2说明出现2次，返回的结果是10
??? 下面来点特殊的测试,先测试nth_appearance，不是说不能为负数吗，我测试nth_appearance=0看下
???
select instr('www.baidu.com', '.',1,0) from dual
select instr('www.baidu.com', '.',-1,0) from dual??? 你们猜结果是什么？结果是ORA-01428:参数'0'超出范围,说明nth_appearance不能为0，只能大于0。
??? 再测试start_position,令start_position=0看下
???
select instr('www.baidu.com', '.',0,1) from dual
select instr('www.baidu.com', '-',0,1) from dual
select instr('www.baidu.com', '.',0,2) from dual
select instr('www.baidu.com', '-',0,2) from dual??? 测试结果都是0;如果把start_position改为>0，结果如下
???
select instr('www.baidu.com', '.',1,1) from dual--4
select instr('www.baidu.com', '-',1,1) from dual--0
select instr('www.baidu.com', '.',1,2) from dual--10
select instr('www.baidu.com', '-',1,2) from dual--0??? 如果把start_position改为<0,结果如下：
???
select instr('www.baidu.com', '.',-1,1) from dual--10
select instr('www.baidu.com', '-',-1,1) from dual--0
select instr('www.baidu.com', '.',-1,2) from dual--4
select instr('www.baidu.com', '-',-1,2) from dual--0
select instr('www.baidu.com', '.',-1,3) from dual--0
select instr('www.baidu.com', '-',-1,3) from dual--0
select instr('www.baidu.com', '.',-1,3) from dual--0
select instr('www.baidu.com', '-',-1,3) from dual--0??? 可以看出instr函数目标字符串在str中不存在时候或者出现次数小于给定的次数时，返回的都是0；
??? 使用中文测试也是一样：
???
select instr('我是中文_测试_数据','_',1,0) from dual;--ORA-01428:参数'0'超出范围
select instr('我是中文_测试_数据','_',1,2) from dual;--8
select instr('我是中文_测试_数据','_',1,3) from dual;--0
select instr('我是中文_测试_数据','_',-1,2) from dual;--5
select instr('我是中文_测试_数据','_',-1,3) from dual;--0??? 下面测试下substr。
???
select substr('www.baidu.com',0,4) from dual
select substr('www.baidu.com',1,4) from dual??? 上面2个执行结果都是'www.'。如果上面是在mysql下测试，结果如下：
???
select substr('www.baidu.com',0,4) from dual--空白字符串
select substr('www.baidu.com',1,4) from dual--www.??? 从这里可以看出mysql中substr开始位置不能为0，而oracle下从0开始或者从1开始结果是一样的，下面继续oracle测试。
??
select substr('www.baidu.com',1) from dual
select substr('www.baidu.com',1,22) from dual??? 返回结果是'www.baidu.com'。
??
select substr('www.baidu.com',-1) from dual
select substr('www.baidu.com',-1,4) from dual
select substr('www.baidu.com',-1,22) from dual??? 返回的结果是'm',说明substr返回的是从开始位置到str最后一位。
??? 使用中文测试如下：
???
select substr('我是中文_测试_数据',1) from dual--我是中文_测试_数据
select substr('我是中文_测试_数据',1,22) from dual--我是中文_测试_数据
select substr('我是中文_测试_数据',-1) from dual--据
select substr('我是中文_测试_数据',-1,4) from dual--据
select substr('我是中文_测试_数据',-1,22) from dual--据??? 从上面的测试可以看出oracle下instr函数和mysql的substring_index函数很像，instr函数已经可以得到下标了，结合substr可以截取子串返回。
??? Mysql的
???
select substring_index('www.baidu.com', '.', 2); ??? Oralce可以这样做:
???
select substr('www.baidu.com', 1, instr('www.baidu.com', '.',1,2)-1) from dual??? instr函数再-1是因为mysqlsubstring_index返回结果不包括.，上面返回的结果都是'www.baidu'。
???
??? Mysql的
???
select substring_index('www.baidu.com', '.', -2);??? Oracle可以这样做:
???
select substr('www.baidu.com', instr('www.baidu.com', '.',-1,2) + 1, length('www.baidu.com'))from dual
select substr('www.baidu.com', instr('www.baidu.com', '.',-1,2) + 1) from dual??? 上面可以不使用length函数，因为oracle的substr默认截取到str最后一位。
???? 中文测试如下:
???? Mysql下面：
????
select substring_index('我是中文_测试_数据','_',1) from dual;--我是中文
select substring_index('我是中文_测试_数据','_',2) from dual;--我是中文_测试
select substring_index('我是中文_测试_数据','_',-2) from dual;--测试_数据
select substring_index('我是中文_测试_数据','_',0) from dual;--空白字符串
select substring_index('我是中文_测试_数据','_',3) from dual;--我是中文_测试_数据
select substring_index('我是中文_测试_数据','.',3) from dual;--我是中文_测试_数据??? Oracle下面：
???
select substr('我是中文_测试_数据', 1, instr('我是中文_测试_数据', '_',1,1)-1) from dual--我是中文
select substr('我是中文_测试_数据', 1, instr('我是中文_测试_数据', '_',1,2)-1) from dual--我是中文_测试
select substr('我是中文_测试_数据', instr('我是中文_测试_数据', '_',-1,2) + 1) from dual--测试_数据
select substr('我是中文_测试_数据', 1, instr('我是中文_测试_数据', '_',1,0)-1) from dual--ORA-01428:参数'0'超出范围
select substr('我是中文_测试_数据', 1, instr('我是中文_测试_数据', '_',1,3)-1) from dual--空白
select substr('我是中文_测试_数据', 1, instr('我是中文_测试_数据', '.',1,3)-1) from dual--空白??? 可以看到我写的这个有点问题，如果想在nth_appearance(>0)大于目标串在str中出现的次数时和mysql一样返回整个字符串，可以这样写：
???
select substr('我是中文_测试_数据',
1,
(select (case
when instr('我是中文_测试_数据', '_', 1, 3) <= 1 then
(select length('我是中文_测试_数据') from dual)
else
(instr('我是中文_测试_数据', '_', 1, 3) - 1)
end)
from dual))
from dual??? 这个在nth_appearance=0时用不了，进一步，考虑到nth_appearance=0时候返回NULL可以这样做，返回''这样的字符串使用substr做不了。
???
select substr('我是中文_测试_数据',
1,
(select (case
when 0 = 0 then
-1
else
case
when instr('我是中文_测试_数据', '_', 1, 3) <= 1 then
(select length('我是中文_测试_数据') from dual)
else
(instr('我是中文_测试_数据', '_', 1, 3) - 1)
end end)
from dual))
from dual??? 上面的sql是不是很丑陋，，我们可以定义一个存储过程封装一下。
????
create or replace procedure my_substring_index(str in varchar2,
delim in varchar2,
in_count in int,
out_str out varchar2) as
v_time int;
v_delim varchar2(20);
begin
if str is null then
out_str := '空白字符串';
elsif length(str) = 0 then
out_str := '空白字符串';
end if;
if delim is null then
v_delim := ' ';
elsif length(delim) = 0 then
v_delim := ' ';
else
v_delim := delim;
end if;
if in_count = 0 then
out_str := '空白字符串';
elsif in_count>0 then
v_time := instr(str, delim, 1, in_count);
else
v_time := instr(str, delim, -1, (-1)*in_count);
end if;
if v_time = 0 then
out_str := str;
end if;
if in_count>0 then
if v_time = 1 then
out_str := '空白字符串';
elsif v_time > 1 then
select substr(str, 1, v_time - 1) into out_str from dual;
end if;
elsif in_count<0 then
if v_time >= 1 then
select substr(str, v_time + 1) into out_str from dual;
end if;
end if;
end my_substring_index;
?? Oracle上面测试过程如下：
??? 在My Object下面的Procedures下找到存储过程my_substring_index，点击右键选择Test.出来Oracle的测试脚本：
???
begin
-- Call the procedure
my_substring_index(str => :str,
delim => :delim,
in_count => :in_count,
out_str => :out_str);
end;
?? 在测试脚本页面下面填上具体值，按F8,不出意外的化可以看到返回值。
?? 如果想自己写脚本可以这样，新建一个sql文件，内容如下：
??
declare
v_str varchar2(50);
v_delim varchar2(20);
v_in_count number;
v_out_str varchar2(20);
begin
v_str:='&str';
v_delim:='&delim';
v_in_count:=&in_count;
my_substring_index(str => v_str,
delim => v_delim,
in_count => v_in_count,
out_str => v_out_str);
dbms_output.put_line('result='||v_out_str);
end;??? 在命令行下运行：
???
SQL> set serveroutput on; SQL> start f:/saveFile/test.sql 16 / result=www.baidu PL/SQL procedure successfully completed
??? 如果想直接在cmd window下测试，可以这样：
???
SQL> set serveroutput on; SQL> var v_str varchar2(50); SQL> var v_delim varchar2(20); SQL> var v_in_count number; SQL> var v_out_str varchar2(50); SQL> exec :v_str:='我是中文_测试_数据'; SQL> exec :v_delim:='_'; SQL> exec :v_in_count:=2; SQL> exec my_substring_index(:v_str,:v_delim,:v_in_count,:v_out_str); SQL> print v_out_str;
????? 以上是我写的oralce函数substr和mysql函数substring_index简单介绍，文章有点简单，但毕竟是原创，转载请注明出处。
????? 全文完，写的不好的地方请见谅，大神请绕过。
?
?
?
?
?
?
?
?
?
?
?
?
??
????
热AI工具
Undresser.AI Undress
人工智能驱动的应用程序，用于创建逼真的裸体照片
AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
AI Hentai Generator
免费生成ai无尽的。
热门文章
热工具
记事本++7.3.1
好用且免费的代码编辑器
SublimeText3汉化版
中文版，非常好用
禅工作室 13.0.1
功能强大的PHP集成开发环境
Dreamweaver CS6
视觉化网页开发工具
SublimeText3 Mac版
神级代码编辑软件(SublimeText3)
热门话题
MySQL：初学者的数据管理易用性
Apr 09, 2025 am 12:07 AM
MySQL适合初学者使用，因为它安装简单、功能强大且易于管理数据。1.安装和配置简单，适用于多种操作系统。2.支持基本操作如创建数据库和表、插入、查询、更新和删除数据。3.提供高级功能如JOIN操作和子查询。4.可以通过索引、查询优化和分表分区来提升性能。5.支持备份、恢复和安全措施，确保数据的安全和一致性。
navicat premium怎么创建
Apr 09, 2025 am 07:09 AM
使用 Navicat Premium 创建数据库：连接到数据库服务器并输入连接参数。右键单击服务器并选择“创建数据库”。输入新数据库的名称和指定字符集和排序规则。连接到新数据库并在“对象浏览器”中创建表。右键单击表并选择“插入数据”来插入数据。
忘记数据库密码，能在Navicat中找回吗？
Apr 08, 2025 pm 09:51 PM
Navicat本身不存储数据库密码，只能找回加密后的密码。解决办法：1. 检查密码管理器；2. 检查Navicat的“记住密码”功能；3. 重置数据库密码；4. 联系数据库管理员。
mysql：简单的概念，用于轻松学习
Apr 10, 2025 am 09:29 AM
MySQL是一个开源的关系型数据库管理系统。1）创建数据库和表：使用CREATEDATABASE和CREATETABLE命令。2）基本操作：INSERT、UPDATE、DELETE和SELECT。3）高级操作：JOIN、子查询和事务处理。4）调试技巧：检查语法、数据类型和权限。5）优化建议：使用索引、避免SELECT*和使用事务。
MySQL和SQL：开发人员的基本技能
Apr 10, 2025 am 09:30 AM
MySQL和SQL是开发者必备技能。1.MySQL是开源的关系型数据库管理系统，SQL是用于管理和操作数据库的标准语言。2.MySQL通过高效的数据存储和检索功能支持多种存储引擎，SQL通过简单语句完成复杂数据操作。3.使用示例包括基本查询和高级查询，如按条件过滤和排序。4.常见错误包括语法错误和性能问题，可通过检查SQL语句和使用EXPLAIN命令优化。5.性能优化技巧包括使用索引、避免全表扫描、优化JOIN操作和提升代码可读性。
Navicat for MariaDB如何查看数据库密码？
Apr 08, 2025 pm 09:18 PM
Navicat for MariaDB 无法直接查看数据库密码，因为密码以加密形式存储。为确保数据库安全，有三个方法可重置密码：通过 Navicat 重置密码，设置复杂密码。查看配置文件（不推荐，风险高）。使用系统命令行工具（不推荐，需要对命令行工具精通）。
navicat怎么新建连接mysql
Apr 09, 2025 am 07:21 AM
可在 Navicat 中通过以下步骤新建 MySQL 连接：打开应用程序并选择“新建连接”（Ctrl N）。选择“MySQL”作为连接类型。输入主机名/IP 地址、端口、用户名和密码。（可选）配置高级选项。保存连接并输入连接名称。
navicat如何执行sql
Apr 08, 2025 pm 11:42 PM
在 Navicat 中执行 SQL 的步骤：连接到数据库。创建 SQL 编辑器窗口。编写 SQL 查询或脚本。单击“运行”按钮执行查询或脚本。查看结果（如果执行查询的话）。
