如何从 MySQL 表中的列值中提取子字符串?
mysqlmysqli database更新于 2025/5/22 18:20:17
我们可以应用任何函数，如 SUBSTRING()、MID() 或 SUBSTR()，从列值中提取子字符串。在这种情况下，我们必须提供列的名称作为函数的第一个参数，即在字符串的位置，我们必须提供列的名称。以下示例将对此进行演示。
示例
假设我们想从"Student"表的"Name"列中提取子字符串，那么可以使用以下不同的函数来完成 −
mysql> Select name, SUBSTR(name,2,4) from student; +---------+------------------+ | name | SUBSTR(name,2,4) | +---------+------------------+ | Gaurav | aura | | Aarav | arav | | Harshit | arsh | | Gaurav | aura | | Yashraj | ashr | +---------+------------------+ 5 rows in set (0.00 sec) mysql> Select name, MID(name,2,4) from student; +---------+---------------+ | name | MID(name,2,4) | +---------+---------------+ | Gaurav | aura | | Aarav | arav | | Harshit | arsh | | Gaurav | aura | | Yashraj | ashr | +---------+---------------+ 5 rows in set (0.00 sec) mysql> Select name, substring(name,2,4) from student; +---------+---------------------+ | name | substring(name,2,4) | +---------+---------------------+ | Gaurav | aura | | Aarav | arav | | Harshit | arsh | | Gaurav | aura | | Yashraj | ashr | +---------+---------------------+ 5 rows in set (0.00 sec)
我们还可以在上述查询中应用条件，如下所示 −
mysql> Select name, substring(name,2,4) from student WHERE address = 'delhi'; +---------+---------------------+ | name | substring(name,2,4) | +---------+---------------------+ | Gaurav | aura | | Harshit | arsh | +---------+---------------------+ 2 rows in set (0.16 sec) mysql> Select name, MID(name,2,4) from student WHERE address = 'delhi'; +---------+---------------+ | name | MID(name,2,4) | +---------+---------------+ | Gaurav | aura | | Harshit | arsh | +---------+---------------+ 2 rows in set (0.00 sec) mysql> Select name, SUBSTR(name,2,4) from student WHERE address = 'delhi'; +---------+------------------+ | name | SUBSTR(name,2,4) | +---------+------------------+ | Gaurav | aura | | Harshit | arsh | +---------+------------------+ 2 rows in set (0.00 sec)
相关文章
有用资源
mysql 参考教程 - 该教程包含有关 mysql 的更多信息:https://www.cainiaomax.com/mysql/
