Split string and do calculation in MySQL
Let's say I gave data as a string:
+----------+
|Size |
+----------+
|15X10 |
|5X4 |
|3 |
|2X6X5 |
+----------+
I want to write this column as integer:
+----------+
|Size |
+----------+
|150 |
|20 |
|3 |
|60 |
+----------+
Some of them are multiplied by three numbers, some of them are just one number. I can split the string, but I can't get MySQL to calculate the number. Thank.
0
source to share
1 answer
Not the cleanest query, 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
this request will only work if you have up to two Xs. See the script here .
+1
source to share