Dennis Dennis - 10 days ago 7
MySQL Question

How to extract 1st and 2nd number from a formatted alphanumeric string using SQL?

Example data in a

weight
column (from 3 rows of data)

+-------------------------+
| weight |
+-------------------------+
| Pump:398kg Motor:0kg |
| Pump:448# Motor:997# |
| Pump:355kg Motor:2495kg |
+-------------------------+


Is there a way to extract the first number from column, and the 2nd number in the column separately?

Note


  • string will always start with
    Pump:

  • string
    Motor:
    will always be in the middle

  • denomination delimiter is either
    #
    or
    kg
    , there are no others

  • weight is always an integer



My goal is to split the numbers each into their own column

i.e.

UPDATE table set weight_pump = ?, weight_motor = ?
UPDATE table set weight_pump = 398, weight_motor = 0


I can solve this using PHP (SELECT each row, do REGEX to extract numbers, write back to the row)

But what I am looking for is a pure SQL solution, if such exists. Preferably a single SQL statement, if exists but I am not opposed to several statements or intermediate steps.

I have seen other answers but they focus on extracting a single number from SQL, I am seeking something more powerful.

Note: it can be also done in two steps (one for "kg", one for "#") to simplify query.

Answer

Here's another solution, using substring_index().

I like this function because it doesn't depend so much on specific character positions.

mysql> SELECT
  SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(weight, ':', -2), 'kg', 1), '#', 1) AS weight_pump,
  SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(weight, ':', -1), 'kg', 1), '#', 1) AS weight_motor 
FROM MyTable;

Output:

+-------------+--------------+
| weight_pump | weight_motor |
+-------------+--------------+
| 398         | 0            |
| 448         | 997          |
| 355         | 2495         |
+-------------+--------------+

This works with UPDATE too:

mysql> UPDATE MyTable
  SET weight_pump = SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(weight, ':', -2), 'kg', 1), '#', 1),
      weight_motor = SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(weight, ':', -1), 'kg', 1), '#', 1);

Read more about this function here: http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_substring-index

Comments