如何利用`SUBSTRING()`与`SUBSTR()`函数截取字符串？ - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

字符串截取大师班：`SUBSTRING()`与`SUBSTR()`函数深度解析

各位同学，大家好！欢迎来到今天的字符串截取大师班。今天我们聚焦于两个非常重要的字符串处理函数：SUBSTRING()和SUBSTR()。它们的功能都是从一个字符串中提取指定的部分，也就是我们常说的字符串截取。虽然名字略有差异，但在不同的数据库系统中，它们的功能和用法可能存在细微的差别。今天我们就来系统地学习这两个函数，让大家彻底掌握字符串截取的技巧。

一、`SUBSTRING()`和`SUBSTR()`概述

SUBSTRING()和SUBSTR()函数用于从字符串中提取子字符串。它们的基本语法通常包含三个要素：

目标字符串： 要进行截取操作的原始字符串。
起始位置： 从哪个位置开始截取（注意：不同的数据库系统，起始位置的索引可能从0或1开始）。
截取长度： 截取多少个字符。

这两个函数在不同数据库系统中的具体实现和语法可能会有所不同，我们需要针对具体的数据库系统进行学习。

二、不同数据库系统中的`SUBSTRING()`和`SUBSTR()`

为了更清晰地了解这两个函数在不同数据库系统中的表现，我们以几个常见的数据库系统为例进行说明：

1. MySQL

在MySQL中，SUBSTRING()和SUBSTR()是完全等价的，可以互换使用。它们的语法如下：

SUBSTRING(str, pos, len)
SUBSTR(str, pos, len)

str: 要提取子字符串的字符串。
pos: 子字符串开始的位置（从1开始计数）。
len: 子字符串的长度。

示例：

SELECT SUBSTRING('Hello World', 1, 5); -- 输出：Hello
SELECT SUBSTR('Hello World', 7, 5);  -- 输出：World

如果len参数被省略，则函数会返回从pos位置开始到字符串结尾的所有字符。

SELECT SUBSTRING('Hello World', 7); -- 输出：World
SELECT SUBSTR('Hello World', 7);  -- 输出：World

2. SQL Server

在SQL Server中，使用的是SUBSTRING()函数，没有SUBSTR()函数。其语法如下：

SUBSTRING ( expression ,start , length )

expression: 要提取子字符串的字符串。
start: 子字符串开始的位置（从1开始计数）。
length: 子字符串的长度。

示例：

SELECT SUBSTRING('Hello World', 1, 5); -- 输出：Hello
SELECT SUBSTRING('Hello World', 7, 5); -- 输出：World

如果length参数超过了剩余字符串的长度，函数会返回从start位置开始到字符串结尾的所有字符。

3. PostgreSQL

PostgreSQL同时支持SUBSTRING()和SUBSTR()函数，并且它们的行为与MySQL类似，可以互换使用。语法如下：

SUBSTRING(string FROM start FOR length)
SUBSTR(string FROM start FOR length)

string: 要提取子字符串的字符串
start: 子字符串开始的位置（从1开始计数）
length: 子字符串的长度

示例：

SELECT SUBSTRING('Hello World' FROM 1 FOR 5); -- 输出: Hello
SELECT SUBSTR('Hello World' FROM 7 FOR 5); -- 输出: World

PostgreSQL还支持使用正则表达式进行更复杂的字符串截取，后续会简单介绍。

4. Oracle

在Oracle中，使用的是SUBSTR()函数，没有SUBSTRING()函数。其语法如下：

SUBSTR(string, start_position, [length])

string: 要提取子字符串的字符串。
start_position: 子字符串开始的位置（从1开始计数）。
length: 子字符串的长度（可选参数）。如果省略，则返回从start_position开始到字符串结尾的所有字符。

示例：

SELECT SUBSTR('Hello World', 1, 5) FROM dual; -- 输出：Hello
SELECT SUBSTR('Hello World', 7, 5) FROM dual; -- 输出：World
SELECT SUBSTR('Hello World', 7) FROM dual; -- 输出：World

总结表格：

数据库系统	函数	起始位置	备注
MySQL	SUBSTRING/SUBSTR	1	完全等价，省略长度参数则截取到字符串结尾。
SQL Server	SUBSTRING	1	没有SUBSTR，省略长度参数，如果长度超过剩余长度，则截取到字符串结尾。
PostgreSQL	SUBSTRING/SUBSTR	1	完全等价，使用`FROM start FOR length` 语法。
Oracle	SUBSTR	1	没有SUBSTRING，省略长度参数则截取到字符串结尾。需要`FROM dual`（在没有实际表时）。

三、`SUBSTRING()`和`SUBSTR()`的进阶用法

掌握了基本用法后，我们来看看更高级的用法，包括结合其他函数实现更复杂的字符串处理。

1. 动态起始位置和长度

我们可以使用变量或表达式来动态地指定起始位置和长度。这在处理动态数据时非常有用。

示例（MySQL）：

SET @start = 3;
SET @length = 4;
SELECT SUBSTRING('Hello World', @start, @length); -- 输出：llo

示例（SQL Server）：

DECLARE @start INT = 3;
DECLARE @length INT = 4;
SELECT SUBSTRING('Hello World', @start, @length); -- 输出：llo

2. 结合LENGTH()/LEN()函数

LENGTH()（MySQL, PostgreSQL）或LEN()（SQL Server）函数可以获取字符串的长度。我们可以结合它来截取字符串的后半部分。

示例（MySQL）：

SELECT SUBSTRING('Hello World', LENGTH('Hello World') - 4); -- 输出：orld

示例（SQL Server）：

SELECT SUBSTRING('Hello World', LEN('Hello World') - 4, 5); -- 输出：orld （需要指定长度，否则只截取到倒数第一位）

示例（Oracle）：

SELECT SUBSTR('Hello World', LENGTH('Hello World') - 4) FROM dual; -- 输出：orld

3. 结合LOCATE()/CHARINDEX()函数

LOCATE()（MySQL, PostgreSQL）或CHARINDEX()（SQL Server）函数可以查找子字符串在字符串中的位置。我们可以结合它来截取特定子字符串之前或之后的部分。

示例（MySQL）：

SELECT SUBSTRING('Hello World', LOCATE(' ', 'Hello World') + 1); -- 输出：World

示例（SQL Server）：

SELECT SUBSTRING('Hello World', CHARINDEX(' ', 'Hello World') + 1, LEN('Hello World')); -- 输出：World

示例（Oracle）：

SELECT SUBSTR('Hello World', INSTR('Hello World', ' ') + 1) FROM dual; -- 输出：World

4. 处理包含特殊字符的字符串

在处理包含特殊字符（如换行符、制表符等）的字符串时，SUBSTRING()和SUBSTR()函数同样适用。

示例（MySQL）：

SELECT SUBSTRING('HellonWorld', 1, 5); -- 输出：Hello

5. 使用负数索引（部分数据库支持）

在一些数据库系统中（例如 PostgreSQL），SUBSTRING()和SUBSTR()函数支持使用负数索引，表示从字符串的末尾开始计数。

示例（PostgreSQL）：

SELECT SUBSTRING('Hello World' FROM -5 FOR 5); -- 输出：World

6. 正则表达式截取（PostgreSQL）

PostgreSQL的SUBSTRING()函数还支持使用正则表达式进行截取。

示例（PostgreSQL）：

SELECT SUBSTRING('Hello 123 World' FROM '[0-9]+'); -- 输出：123

这条语句会提取字符串中第一个匹配正则表达式[0-9]+（一个或多个数字）的子字符串。

四、常见应用场景

SUBSTRING()和SUBSTR()函数在实际开发中有很多应用场景，下面列举一些常见的例子：

提取文件名： 从完整的文件路径中提取文件名。

-- MySQL
SELECT SUBSTRING('/path/to/my/file.txt', LOCATE('/', REVERSE('/path/to/my/file.txt'))+1); -- 输出：file.txt
-- SQL Server
SELECT REVERSE(SUBSTRING(REVERSE('/path/to/my/file.txt'), 1, CHARINDEX('/', REVERSE('/path/to/my/file.txt')) - 1)); -- 输出：file.txt
-- Oracle
SELECT SUBSTR('/path/to/my/file.txt', LENGTH('/path/to/my/file.txt') - INSTR(REVERSE('/path/to/my/file.txt'), '/') + 2) FROM dual; -- 输出：file.txt

提取URL中的域名： 从URL中提取域名。

-- MySQL
SELECT SUBSTRING('https://www.example.com/path/to/page', LOCATE('//', 'https://www.example.com/path/to/page') + 2, LOCATE('/', 'https://www.example.com/path/to/page', LOCATE('//', 'https://www.example.com/path/to/page') + 2) - LOCATE('//', 'https://www.example.com/path/to/page') - 2); -- 输出：www.example.com

截取用户名的前几个字符： 用于显示用户名的缩略形式。
```
SELECT SUBSTRING(username, 1, 3) FROM users;
```

处理电话号码： 提取电话号码的区号或后四位。

-- MySQL, 假设电话号码格式为 (XXX) XXX-XXXX
SELECT SUBSTRING('(123) 456-7890', 2, 3); -- 输出：123 (区号)
SELECT SUBSTRING('(123) 456-7890', 9, 4); -- 输出：7890 (后四位)

数据清洗： 从包含错误格式的数据中提取有效信息。例如，从 "Name: John Doe" 提取 "John Doe"。
```
-- MySQL
SELECT SUBSTRING('Name: John Doe', LOCATE(': ', 'Name: John Doe') + 2); -- 输出：John Doe
```

数据转换： 将日期格式从一种形式转换为另一种形式。例如，将 "2023-10-26" 转换为 "10/26/2023"。

-- MySQL
SELECT CONCAT(SUBSTRING('2023-10-26', 6, 2), '/', SUBSTRING('2023-10-26', 9, 2), '/', SUBSTRING('2023-10-26', 1, 4)); -- 输出：10/26/2023

五、注意事项

在使用SUBSTRING()和SUBSTR()函数时，需要注意以下几点：

起始位置： 不同的数据库系统对起始位置的索引方式不同。MySQL、SQL Server、PostgreSQL和Oracle都从1开始计数。
长度： 如果截取长度超过了剩余字符串的长度，不同的数据库系统可能会有不同的处理方式。有些数据库系统会返回从起始位置到字符串结尾的所有字符，有些可能会报错。
空字符串： 如果目标字符串为空字符串，SUBSTRING()和SUBSTR()函数通常会返回空字符串。
NULL值： 如果目标字符串为NULL，SUBSTRING()和SUBSTR()函数通常会返回NULL。
性能： 对于非常长的字符串，频繁使用SUBSTRING()和SUBSTR()函数可能会影响性能。可以考虑使用其他更高效的字符串处理方法。
字符集： 注意字符集的影响，尤其是在处理包含多字节字符的字符串时。确保使用正确的字符集，避免截取结果出现乱码。

六、案例分析

我们来看一个稍微复杂一些的案例：假设我们有一个包含逗号分隔数据的字符串，例如 "apple,banana,orange,grape"。我们需要提取出每个水果的名字。

示例（MySQL）：

SET @str = 'apple,banana,orange,grape';
SET @delimiter = ',';
SET @count = LENGTH(@str) - LENGTH(REPLACE(@str, @delimiter, '')) + 1; -- 计算逗号的数量 + 1，得到水果的数量

SET @i = 1;
WHILE @i <= @count DO
  SET @start = 1;
  SET @end = LOCATE(@delimiter, @str, @start);
  IF @end = 0 THEN
    SET @end = LENGTH(@str) + 1;
  END IF;

  SET @fruit = SUBSTRING(@str, @start, @end - @start);
  SELECT @fruit; -- 输出当前水果的名字

  SET @str = SUBSTRING(@str, @end + 1); -- 更新字符串，移除已经提取的水果
  SET @i = @i + 1;
END WHILE;

这个例子使用了循环和LOCATE()函数来定位逗号的位置，然后使用SUBSTRING()函数提取水果的名字。

七、使用场景拓展

除了上述列举的场景外， SUBSTRING()和SUBSTR()函数在以下场景也能发挥重要作用：

日志分析： 从日志文件中提取关键信息，如时间戳、错误代码等。
数据验证： 验证数据的格式是否符合要求，例如验证邮政编码、身份证号码等。
生成唯一标识符： 结合其他函数，生成基于字符串的唯一标识符。
文本搜索和替换： 与其他字符串函数结合，实现更复杂的文本搜索和替换功能。

八、总结

SUBSTRING()和SUBSTR()函数是字符串处理的基础工具，掌握它们对于进行数据清洗、转换和分析至关重要。通过理解它们在不同数据库系统中的差异，并结合其他字符串函数，我们可以灵活地处理各种字符串操作。记住，多练习，多实践，才能真正掌握这些技巧。

希望今天的课程对大家有所帮助！祝大家编程愉快！

字符串截取大师班：SUBSTRING()与SUBSTR()函数深度解析

一、SUBSTRING()和SUBSTR()概述

二、不同数据库系统中的SUBSTRING()和SUBSTR()

三、SUBSTRING()和SUBSTR()的进阶用法

四、常见应用场景

五、注意事项

六、案例分析

七、使用场景拓展

八、总结

发表回复 取消回复

字符串截取大师班：`SUBSTRING()`与`SUBSTR()`函数深度解析

一、`SUBSTRING()`和`SUBSTR()`概述

二、不同数据库系统中的`SUBSTRING()`和`SUBSTR()`

三、`SUBSTRING()`和`SUBSTR()`的进阶用法

发表回复取消回复