基于regexp的mysql计算字段

I'm trying to update a field based on another field.

我正在尝试根据另一个字段更新字段。

Data looks like this:

数据如下所示：

id|Measurement

1|15x52 9x2

Ultimately, I'd like the calculated field like:

最终，我喜欢计算字段，如：

id|Measurement|calcField

1|15x52 9x2|798

Measurement may have "Hello, George", etc. so only if we find a [digit]x[digit] we should calculate.

测量可能有“你好，乔治”等，所以只有当我们找到[数字] x [数字]我们才应该计算。

I would be happy with:

我很高兴：

id|Measurement|calcField

1|15x52 9x2|780 18

Any assistance will be appreciated! Thank you.

任何帮助将不胜感激！谢谢。

2 个解决方案

#1

You have to use LOCATE() to find the space and x characters, get the substrings between them, and do the arithmetic with those substrings. Unfortunately, this is really verbose when there's no regexp matcher that can extract parts of the string.

您必须使用LOCATE（）来查找空格和x字符，获取它们之间的子字符串，并使用这些子字符串进行算术运算。不幸的是，当没有可以提取字符串部分的正则表达式匹配器时，这真的很冗长。

IF(Measurement REGEXP '^[0-9]+x[0-9]+ [0-9]+x[0-9]+$',
   LEFT(Measurement, LOCATE('x', Measurement)-1) *
   SUBSTR(Measurement, LOCATE('x', Measurement)+1, LOCATE(' ', Measurement)-LOCATE('x', Measurement))
   +
   SUBSTR(Measurement, LOCATE(' ', Measurement)+1, LOCATE('x', Measurement, LOCATE(' ', Measurement))-LOCATE(' ', Measurement)-1) *
   SUBSTR(Measurement, LOCATE('x', Measurement, LOCATE(' ', Measurement))+1), 0)

if you have to handle an arbitrary number of `###x

如果你必须处理任意数量的`### x

#2

This solved my problem. Though not directly in MySQL, I am pulling from MySQL field. I hope it helps someone.

这解决了我的问题。虽然不是直接在MySQL，我从MySQL领域拉。我希望它对某人有帮助。

$select_query = $stmt->fetch(PDO::FETCH_OBJ);

$output_array = array();

$search = preg_match_all("/\b[0-9]+x[0-9]*\b/", $select_query->GridCardMeasurements, $output_array);
$sfTotal = 0;

foreach ($output_array[0] as $valuex) {
  $sf = explode("x", $valuex);
  $sfTotal += $sf[0]*$sf[1];
}