mysql中REGEXP_SUBSTR的等效项是什么? [英] What is the equivalent of REGEXP_SUBSTR in mysql?

问题描述

我想从表格的字符串列中提取一个单词.

description
===========================
abc order_id: 2 xxxx yyy aa
mmm order_id: 3 nn kk yw

预期结果集

order_id
===========================
2
3

表最多可包含100行，文本长度约为256个字符，列中始终存在一个order_id.因此性能不是问题.

在Oracle中，我可以使用REGEXP_SUBSTR来解决此问题.我要如何在MySQL中解决这个问题?

编辑1

我正在使用LOCATE和SUBSTR解决此问题.代码很丑.编写代码十分钟后，我咒骂那个编写了这么丑陋代码的家伙.

我在MySQL文档中找不到REGEXP_SUBSTR函数.但我希望它存在.

答案:为什么无法优化表格?为什么数据以这种愚蠢的方式存储?

我给出的示例仅表示我要解决的问题.在实际情况下，我正在使用基于数据库的第三方排队软件来执行异步任务.队列将Ruby对象序列化为文本.我无法控制表结构或数据格式.队列中的任务可以重复执行.在我们的测试设置中，由于数据过时，一些重复执行的任务失败了.我必须删除这些任务以防止错误.这种错误并不常见，因此我不想维护标准化的影子表.

解决方案

我没有在MySQL文档中找到 REGEXP_SUBSTR 函数.但是我希望它存在."

是的，从MySQL 8.0开始，它受支持. 正则表达式:

REGEXP_SUBSTR(expr, pat[, pos[, occurrence[, match_type]]])

返回与模式pat指定的正则表达式匹配的字符串expr的子字符串，如果不匹配，则返回NULL.如果expr或pat为NULL，则返回值为NULL.

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_id present. So performance is not an issue.

In Oracle, I can use REGEXP_SUBSTR for this problem. How would I solve this in MySQL?

Edit 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.

I didn't find the REGEXP_SUBSTR function in MySQL docs. But I am hoping that it exists..

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.

解决方案

"I didn't find the REGEXP_SUBSTR function in MySQL docs. But I am hoping that it exists.."

Yes, starting from MySQL 8.0 it is supported. Regular Expressions:

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.

这篇关于mysql中REGEXP_SUBSTR的等效项是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！