在 MySQL 中获取给定字符串中最后一个子字符串的索引?
mysqlmysqli database
要获取给定字符串中最后一个子字符串的索引，请使用 char_length() 函数。首先，我们需要计算字符串长度并从整个长度中减去最后一个子字符串的长度。长度差就是子字符串的索引。
语法
语法如下 −
select CHAR_LENGTH(yourColumnName) - LOCATE('yourDelimiter ',
REVERSE(yourColumnName))+1 as anyVariableName
from yourTableName;
为了理解上述语法，我们首先创建一个表。创建表的查询如下 −
mysql> create table SubStringIndexDemo -> ( -> Words varchar(200) -> ); Query OK, 0 rows affected (0.57 sec)
使用 insert 命令在表中插入一些记录。查询如下 −
mysql> insert into SubStringIndexDemo values('This is MySQL Query');
Query OK, 1 row affected (0.13 sec)
mysql> insert into SubStringIndexDemo values('MySQL is a Relational Database');
Query OK, 1 row affected (0.34 sec)
mysql> insert into SubStringIndexDemo values('Java is a programming language');
Query OK, 1 row affected (0.11 sec)
mysql> insert into SubStringIndexDemo values('Spring is a Framework');
Query OK, 1 row affected (0.20 sec)
使用 select 命令显示表中的所有记录。查询如下 −
mysql> select *from SubStringIndexDemo;
输出
+--------------------------------+ | Words | +--------------------------------+ | This is MySQL Query | | MySQL is a Relational Database | | Java is a programming language | | Spring is a Framework | +--------------------------------+ 4 rows in set (0.00 sec)
以下是可用于获取最后一个子字符串的索引的查询::
mysql> select CHAR_LENGTH(Words) - LOCATE(' ', REVERSE(Words))+1 as
PositionOfLastSubstring
-> from SubStringIndexDemo;
Output
+-------------------------+ | PositionOfLastSubstring | +-------------------------+ | 14 | | 22 | | 22 | | 12 | +-------------------------+ 4 rows in set (0.00 sec)
