mysql 中 REGEXP_SUBSTR 的等价物是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2742650/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the equivalent of REGEXP_SUBSTR in mysql?
提问by Harish Shetty
I want to extract a word from a string column of a table.
我想从表的字符串列中提取一个单词。
description
===========================
abc order_id: 2 xxxx yyy aa
mmm order_id: 3 nn kk yw
Expected result set
预期结果集
order_id
===========================
2
3
Table will at most have 100 rows, text length is ~256 char and column always has one order_idpresent. So performance is not an issue.
表最多有 100 行，文本长度是 ~256 个字符，列总是有一个order_id。所以性能不是问题。
In Oracle, I can use REGEXP_SUBSTRfor this problem. How would I solve this in MySQL?
在 Oracle 中，我可以REGEXP_SUBSTR针对这个问题使用。我将如何在 MySQL 中解决这个问题？
Edit 1
编辑 1
I am using LOCATE and SUBSTR to solve the problem. The code is ugly. Ten minutes after writing the code, I am cursing the guy who wrote such an ugly code.
我正在使用 LOCATE 和 SUBSTR 来解决问题。代码很丑。写代码十分钟后，我在诅咒写了这么丑陋代码的家伙。
I didn't find the REGEXP_SUBSTR function in MySQL docs. But I am hoping that it exists..
我在 MySQL 文档中没有找到 REGEXP_SUBSTR 函数。但我希望它存在..
Answer to : Why cant the table be optimized? Why is the data stored in such a dumb fashion?
回答：为什么不能优化表？为什么数据以如此愚蠢的方式存储？
The example I gave just denotes the problem I am trying to solve. In real scenario, I am using a DB based 3rd party queuing software for executing asynchronous tasks. The queue serializes the Ruby object as text. I have no control over the table structure OR the data format. The tasks in the queue can be recurring. In our test setup, some of the recurring tasks are failing because of stale data. I have to delete these tasks to prevent the error. Such errors are not common, hence I don't want to maintain a normalized shadow table.
我给出的例子只是表示我试图解决的问题。在实际场景中，我使用基于数据库的 3rd 方排队软件来执行异步任务。队列将 Ruby 对象序列化为文本。我无法控制表结构或数据格式。队列中的任务可以重复执行。在我们的测试设置中，一些重复性任务由于数据陈旧而失败。我必须删除这些任务以防止出现错误。此类错误并不常见，因此我不想维护规范化的影子表。
采纳答案by Lukasz Szozda
"I didn't find the REGEXP_SUBSTRfunction in MySQL docs. But I am hoping that it exists.."
“我没有在 MySQL 文档中找到REGEXP_SUBSTR函数。但我希望它存在..”
Yes, starting from MySQL 8.0 it is supported. Regular Expressions:
是的，从 MySQL 8.0 开始支持它。正则表达式：
REGEXP_SUBSTR(expr, pat[, pos[, occurrence[, match_type]]])Returns the substring of the string expr that matches the regular expression specified by the pattern pat, NULL if there is no match. If expr or pat is NULL, the return value is NULL.
REGEXP_SUBSTR(expr, pat[, pos[, occurrence[, match_type]]])返回与模式 pat 指定的正则表达式匹配的字符串 expr 的子字符串，如果不匹配则返回 NULL。如果 expr 或 pat 为 NULL，则返回值为 NULL。
回答by Julien Hoarau
Like Konerak said, there is no equivalent of REGEXP_SUBSTR in MySql. You could do what you need using SUBSTRING logic, but it is ugly :
就像 Konerak 所说的，MySql 中没有 REGEXP_SUBSTR 的等价物。您可以使用 SUBSTRING 逻辑执行您需要的操作，但它很难看：
SELECT
SUBSTRING(lastPart.end, 1, LOCATE(' ', lastPart.end) - 1) AS orderId
FROM
(
SELECT
SUBSTRING(dataset.description, LOCATE('order_id: ', dataset.description) + LENGTH('order_id: ')) AS end
FROM
(
SELECT 'abc order_id: 2 xxxx yyy aa' AS description
UNION SELECT 'mmm order_id: 3 nn kk yw' AS description
UNION SELECT 'mmm order_id: 1523 nn kk yw' AS description
) AS dataset
) AS lastPart
Edit:You could try this user defined functionproviding access to perl regex in MySql
编辑：您可以尝试使用此用户定义的函数来提供对 MySql 中的 perl 正则表达式的访问
SELECT
PREG_CAPTURE( '/.*order_id:\s(\d+).*/', dataset.description,1)
FROM
(
SELECT 'abc order_id: 2 xxxx yyy aa' AS description
UNION SELECT 'mmm order_id: 3 nn kk yw' AS description
UNION SELECT 'mmm order_id: 1523 nn kk yw' AS description
) AS dataset
回答by Steven Universe
or you can do this and save yourself the ugliness :
或者你可以这样做并避免自己的丑陋：
select SUBSTRING_INDEX(SUBSTRING_INDEX('habc order_id: 2 xxxx yyy aa',' ',3),' ',-1);
回答by Konerak
There is no MySQL equivalent. The MySQL REGEXP can be used for matching strings, but not for transforming them.
没有 MySQL 等价物。MySQL REGEXP 可用于匹配字符串，但不能用于转换它们。
You can either try to work with stored procedures and a lot of REPLACE/SUBSTRING logic, or do it in your programming language - which should be the easiest option.
您可以尝试使用存储过程和大量 REPLACE/SUBSTRING 逻辑，或者使用您的编程语言进行操作 - 这应该是最简单的选择。
But are you sure your data format is well chosen? If you need the order_id, wouldn't it make sense to store it in a different column, so you can put indexes, use joins and the likes?
但是您确定您的数据格式选择得很好吗？如果您需要 order_id，将它存储在不同的列中是否有意义，以便您可以放置索引、使用连接等？
