在MySQL中的定界符之间搜索文本？
您需要与LOCATE()一起使用SUBSTR()。以下语法将在定界符后找到单词。在这里，定界符是冒号（:)，您可以使用另一个，即由您自己决定。语法如下-
SELECT
SUBSTR(yourColumnName,
LOCATE(':',yourColumnName)+1,
(CHAR_LENGTH(yourColumnName) - LOCATE(':',REVERSE(yourColumnName)) - LOCATE(':',yourColumnName))) AS anyAliasName
FROM yourTableName;为了理解上述语法，让我们创建一个表。创建表的查询如下-
mysql> create table SearchTextBetweenDelimitersDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Words longtext, -> PRIMARY KEY(Id) -> );
现在，您可以使用insert命令在表中插入一些记录。查询如下-
mysql> insert into SearchTextBetweenDelimitersDemo(Words) values('Object:Oriented:Programming');
mysql> insert into SearchTextBetweenDelimitersDemo(Words) values('C++:Java:C#');
mysql> insert into SearchTextBetweenDelimitersDemo(Words) values('SQLServer:MySQL:Oracle');
mysql> insert into SearchTextBetweenDelimitersDemo(Words) values('SQL SQLServer:MySQL:Oracle');使用select语句显示表中的所有记录。查询如下-
mysql> select *from SearchTextBetweenDelimitersDemo;
以下是输出-
+----+-----------------------------+ | Id | Words | +----+-----------------------------+ | 1 | Object:Oriented:Programming | | 2 | C++:Java:C# | | 3 | SQLServer:MySQL:Oracle | | 4 | SQL SQLServer:MySQL:Oracle | +----+-----------------------------+ 4 rows in set (0.00 sec)
这是查询并显示定界符之间的单词的查询-
mysql> SELECT
-> SUBSTR(Words,
-> LOCATE(':',Words)+1,
-> (CHAR_LENGTH(Words) - LOCATE(':',REVERSE(Words)) - LOCATE(':',Words))) AS Text
-> FROM SearchTextBetweenDelimitersDemo;以下是输出-
+----------+ | Text | +----------+ | Oriented | | Java | | MySQL | | MySQL | +----------+ 4 rows in set (0.00 sec)