热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

SQL字符串操作:深入解析instr、substr和like函数的应用与区别

在进行SQL字符串操作时,经常会用到`instr`、`substr`和`like`函数。本文详细解析了这些函数的应用场景和区别。特别是`like`函数在处理文件路径匹配时可能会遇到的问题,如通配符`_`和`%`的使用。其中,`%`可以匹配零个或多个任意字符,而`_`则匹配任意单个字符。通过实例和文档解析,帮助读者更好地理解和应用这些函数。
前言

最近做测试时发现 SQLite like 匹配文件路径有时会有问题,查文档才知道是没有处理 like 字符串中的通配符 _ 下划线和 % 百分号。% 匹配零个及多个任意字符; _ 与任意单字符匹配,如:

select filepath from table where filepath like 'D:/#/123%/%';

本来是想查找文件夹 123% 下的文件,但百分号被识别为了通配符,结果可能是:

《SQL字符串查找,instr、substr与like》

这时候有几种替代选择,使用 instr 来匹配,或者 like 语句加 escape 转义,substr 截取判断等。但是还要注意不同的数据库支持的 SQL 语句有区别,需要查对应的数据库参考,我主要是以 SQLite 进行测试。

--instr = 1 表示以搜索的字符串打头
select filepath from table where instr(filepath,'D:/#/123%/')=1;
--escape '\' 表示转义字符 '\' 后面的 % 或 _ 就不作为通配符了
select filepath from table where filepath like 'D:/#/123\%/%' escape '\';
--SQLite substr用于截取字符串,这里从第一个字符截取相同长度进行比较
select filepath from table where substr(filepath,1,length('D:/#/123%/'))='D:/#/123%/';
instr函数

instr 函数是一个字符串处理函数,返回子字符串在源字符串中的位置,如果在源串中没有找到子串,则返回0。

SQLite 中的定义(MYSQL类似):

instr( X , Y )

instr(X,Y) 函数查找字符串 X 中第一次出现的字符串 Y 并返回先前字符数加 1,如果在 X 中找不到 Y,则返回 0。或者,如果 X 和 Y 都是 BLOB,则 instr (X,Y) 返回比第一次出现 Y 之前的字节数多一个字节,如果 Y 不在 X 内的任何位置出现,则返回 0。如果 instr(X,Y) 的参数 X 和 Y 都不是 NULL 并且是不是 BLOB,则两者都被解释为字符串。如果 instr(X,Y) 中的 X 或 Y 为 NULL,则结果为 NULL。

Oracle/PLSQL 中的定义:

instr( str, substr[, position [, appearance ] ] )   

返回子字符串在源字符串中的位置(字符串位置从1开始,而不是从0开始)

str 源字符串

substr 子字符串

position 检索位置,可省略(默认为1),参数为正时,从左向右检索,参数为负时,从右向左检索

occurrence 检索子串出现次数(即子串在源串第几次出现),可省略(默认为1),值只能为正整数,否则会报错

示例

--下列语句结果:3
SELECT INSTR('hello world', 'l') FROM DUAL; 
--下列语句结果:10 (从左向右第5位开始检索'l'在'hello world'中出现的位置)
SELECT INSTR('hello world', 'l', 5) FROM DUAL; 
--下列语句结果:10 (从右向左第1位开始检索'l'在'hello world'中出现的位置)
SELECT INSTR('hello world', 'l', -1) FROM DUAL; 
--下列语句结果:4 (从左向右第2位开始检索'l'在'hello world'中第2次出现的位置)
SELECT INSTR('hello world', 'l', 2, 2) FROM DUAL; 
--下列语句结果:0 (从右向左第3位开始检索'l'在'hello world'中第3次出现的位置)
SELECT INSTR('hello world', 'l', -3, 3) FROM DUAL; 
substr函数

substr 的功能为截取字符串子串

SQLite 中的定义:

substr( X , Y , Z )
substr( X , Y )
substring( X , Y , Z )
substring( X , Y )

substr(X,Y,Z) 函数返回输入字符串 X 的子字符串,该子字符串以第 Y 个字符开头,长度为 Z 个字符。如果省略 Z,则 substr(X,Y) 返回从第 Y 个开始到字符串 X 末尾的所有字符。X 最左边的字符是数字 1。如果 Y 是负数,则通过从右侧而不是左侧开始计数来找到子字符串的第一个字符。如果 Z 为负,则返回第 Y 个字符之前的 abs(Z) 字符。如果 X 是字符串,则字符索引指的是实际的 UTF-8 字符。如果 X 是 BLOB,则索引指的是字节。

 从 SQLite 3.34 版开始,“substring()”是“substr()”的别名。

--以filepath字段第一个字符截取相同长度进行比较
select filepath from table where substr(filepath,1,length('D:/#/123%/'))='D:/#/123%/';
like操作符

like 操作符可在 where 子句中搜索指定内容,一般搭配通配符 _ 和 % 进行模糊查询。其中 % 百分号代表零个或多个字符,_ 下划线代表单个字符。

如果要查的字符串包含 % 或者 _ 怎么办呢?使用 escape 转义,转义字符后面的 % 或 _ 就不作为通配符了,但也要考虑被转义的字符是否会出现在字符串中。

--在STUDENTTAB表中查询STUNAME中含有字符“张”的学员
SELECT * FROM STUDENTTAB WHERE STUNAME LIKE '%张%';
--在STUDENTTAB表中查询STUNAME中以“张”开头,名字长度为2的学员(即“张三”、“张四”,而不会检测出“张三三”)
SELECT * FROM STUDENTTAB WHERE STUNAME LIKE '张_';
--可以正确识别字符串中百分号
select filepath from table where filepath like 'D:/#/123\%/%' escape '\';

instr(title,'手册')>0 相当于 title like '%手册%'
instr(title,'手册')=1 相当于 title like '手册%'
instr(title,'手册')=0 相当于 title not like '%手册%'

instr 与 like 比较

  • instr>0 和 like、instr=0 和 not like 一般来说查询的结果相同(不考虑特殊字符)。
  • instr 是一个函数,可以建立函数索引,如果过滤的条件有索引,那么 instr 就可以提高性能。
  • like 查询时,以 ‘%’ 开头,列所加的索引是不起作用的。
  • 在没有索引的前提下,当数据量比较大时,instr 要比 like 效率高。
参考

SQLite 文档:https://www.sqlite.org/lang_corefunc.html

博客 instr 与 like 区别:https://blog.csdn.net/lanmuhhh2015/article/details/79216804


推荐阅读
  • 在MySQL中更新密码时,首先需要在DOS窗口中切换到mysql安装目录,并使用`--skip-grant-tables`参数启动MySQL服务,以跳过权限表验证。接着,在MySQL命令行中执行相应的SQL语句来设置新密码。完成密码更新后,重启MySQL服务以使更改生效。此外,对于电脑快捷方式的修改,可以通过右键点击快捷方式,选择“属性”,在弹出的窗口中进行路径或目标的修改,最后点击“应用”和“确定”保存更改。 ... [详细]
  • 通过 NuGet 获取最新版本的 Rafy 框架及其详细文档
    为了帮助开发者更便捷地使用Rafy领域实体框架,我们已将最新版的Rafy框架程序集上传至nuget.org,并同步发布了最新版本的Rafy SDK至Visual Studio。此外,我们还提供了详尽的文档和示例,以确保开发者能够快速上手并充分利用该框架的强大功能。 ... [详细]
  • 探索聚类分析中的K-Means与DBSCAN算法及其应用
    聚类分析是一种用于解决样本或特征分类问题的统计分析方法,也是数据挖掘领域的重要算法之一。本文主要探讨了K-Means和DBSCAN两种聚类算法的原理及其应用场景。K-Means算法通过迭代优化簇中心来实现数据点的划分,适用于球形分布的数据集;而DBSCAN算法则基于密度进行聚类,能够有效识别任意形状的簇,并且对噪声数据具有较好的鲁棒性。通过对这两种算法的对比分析,本文旨在为实际应用中选择合适的聚类方法提供参考。 ... [详细]
  • 2016-2017学年《网络安全实战》第三次作业
    2016-2017学年《网络安全实战》第三次作业总结了教材中关于网络信息收集技术的内容。本章主要探讨了网络踩点、网络扫描和网络查点三个关键步骤。其中,网络踩点旨在通过公开渠道收集目标信息,为后续的安全测试奠定基础,而不涉及实际的入侵行为。 ... [详细]
  • TypeScript 实战分享:Google 工程师深度解析 TypeScript 开发经验与心得
    TypeScript 实战分享:Google 工程师深度解析 TypeScript 开发经验与心得 ... [详细]
  • 在过去,我曾使用过自建MySQL服务器中的MyISAM和InnoDB存储引擎(也曾尝试过Memory引擎)。今年初,我开始转向阿里云的关系型数据库服务,并深入研究了其高效的压缩存储引擎TokuDB。TokuDB在数据压缩和处理大规模数据集方面表现出色,显著提升了存储效率和查询性能。通过实际应用,我发现TokuDB不仅能够有效减少存储成本,还能显著提高数据处理速度,特别适用于高并发和大数据量的场景。 ... [详细]
  • 在今天的实践中,我深入学习了网页图像抓取技术,通过编写爬虫程序批量获取网站上的图片资源。具体来说,我选择了一个包含大量高质量图片的网站作为练习对象,并成功实现了将这些图片批量下载到本地存储。这一过程不仅提升了我对爬虫技术的理解,还增强了我的编程能力。 ... [详细]
  • 开发笔记:深入解析Android自定义控件——Button的72种变形技巧
    开发笔记:深入解析Android自定义控件——Button的72种变形技巧 ... [详细]
  • 为了向用户提供虚拟应用程序,通常会在基础架构中部署StoreFront或Web Interface。为了确保安全的远程访问,通常需要在DMZ中配置Secure Gateway或Access Gateway。本文详细对比了这两种界面工具的功能特性,包括用户管理、安全性、性能优化等方面,为企业选择合适的解决方案提供了全面的参考。 ... [详细]
  • 深入浅出解读奇异值分解,助你轻松掌握核心概念 ... [详细]
  • Java Web开发中的JSP:三大指令、九大隐式对象与动作标签详解
    在Java Web开发中,JSP(Java Server Pages)是一种重要的技术,用于构建动态网页。本文详细介绍了JSP的三大指令、九大隐式对象以及动作标签。三大指令包括页面指令、包含指令和标签库指令,它们分别用于设置页面属性、引入其他文件和定义自定义标签。九大隐式对象则涵盖了请求、响应、会话、应用上下文等关键组件,为开发者提供了便捷的操作接口。动作标签则通过预定义的动作来简化页面逻辑,提高开发效率。这些内容对于理解和掌握JSP技术具有重要意义。 ... [详细]
  • 在Python编程中,探讨了并发与并行的概念及其区别。并发指的是系统同时处理多个任务的能力,而并行则指在同一时间点上并行执行多个任务。文章详细解析了阻塞与非阻塞操作、同步与异步编程模型,以及IO多路复用技术的应用。通过模拟socket发送HTTP请求的过程,展示了如何创建连接、发送数据和接收响应,并强调了默认情况下socket的阻塞特性。此外,还介绍了如何利用这些技术优化网络通信性能和提高程序效率。 ... [详细]
  • 本文介绍了使用 Python 编程语言高效抓取微博文本和动态网页图像数据的方法。通过详细的示例代码,展示了如何利用爬虫技术获取微博内容和动态图片,为数据采集和分析提供了实用的技术支持。对于对网络数据抓取感兴趣的读者,本文具有较高的参考价值。 ... [详细]
  • 如何构建基于Spring MVC框架的Java Web应用项目
    在构建基于Spring MVC框架的Java Web应用项目时,首先应创建一个新的动态Web项目。接着,需将必要的JAR包导入至WebContent/WEB-INF/lib目录下,确保包括Spring核心库及相关依赖。如遇缺失的JAR包,可向社区求助或通过Maven等工具自动下载。正确配置后,即可开始搭建应用结构与功能模块。 ... [详细]
  • 利用PaddleSharp模块在C#中实现图像文字识别功能测试
    PaddleSharp 是 PaddleInferenceCAPI 的 C# 封装库,适用于 Windows (x64)、NVIDIA GPU 和 Linux (Ubuntu 20.04) 等平台。本文详细介绍了如何使用 PaddleSharp 在 C# 环境中实现图像文字识别功能,并进行了全面的功能测试,验证了其在多种硬件配置下的稳定性和准确性。 ... [详细]
author-avatar
章小胭
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有