在 MySQL 中拆分字符串并进行计算 [英] Split String and Do Calculation in MySQL

问题描述

假设我已将数据作为字符串提供:

Let's say I have given data as string:

+----------+
|Size      |
+----------+
|15X10     |
|5X4       |
|3         |
|2X6X5     |
+----------+

我想把这个列写成这样的整数:

I want to write this column as integer like this:

 +----------+
 |Size      |
 +----------+
 |150       |
 |20        |
 |3         |
 |60        |
 +----------+

有些是三个数字的乘积，有些只是一个数字.我可以拆分字符串，但不能让 MySQL 计算数字.谢谢.

Some of them are multiply of three numbers, some of them just one number. I can split the string but cannot make MySQL to calculate the number. Thank you.

推荐答案

不是有史以来最干净的查询，但您可以使用 SUBSTRING_INDEX:

Not the cleanest query ever, but you can use SUBSTRING_INDEX:

SELECT
  Size,
  CASE
    WHEN Size LIKE '%X%X%' THEN
       SUBSTRING_INDEX(Size, 'X', 1)*
       SUBSTRING_INDEX(SUBSTRING_INDEX(Size, 'X', 2), 'X', -1)*
       SUBSTRING_INDEX(SUBSTRING_INDEX(Size, 'X', 3), 'X', -1)
    WHEN Size LIKE '%X%' THEN
       SUBSTRING_INDEX(Size, 'X', 1)*
       SUBSTRING_INDEX(SUBSTRING_INDEX(Size, 'X', 2), 'X', -1)
    ELSE Size
  END AS multipied_size
FROM
  sizes

此查询仅在您最多有两个 X 时才有效.请在 此处查看小提琴一>.

this query will work only if you have up to two X. Please see a fiddle here.

这篇关于在 MySQL 中拆分字符串并进行计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！