Question 113060 in Mysql Question posted in
The official MySQL documentation can be found here.

How can I extract a number from string using regex with various formats (MySQL)?

I have a MySQL data table which stores metadata for client transactions. I am writing a query to extract a number out of the metadata column, which is essentially a JSON stored as a string.

I am trying to find ‘clients’ and extract the first number after clients. The data can be stored in several different ways; see the examples below:

  • ..."type":"temp","typeOther":"","clients":"2","hours":"5",...
  • ..."id":31457,"clients":2,"cancel":false...

I’ve tried the following Regexp:

  • (?<=clients.....)[0-9]+
  • (?<=clients...)[0-9]*(?=[[^:digit:]])

And I’ve tried the following json_extract, but it returned a null value:

  • json_extract(rd.meta, '$.clients')

The regexp functions do work, but the first one only works on the first example, while the second only works on the second example.

What regexp should I use such that it’s dynamic and will pull the number nested between two non-word char sets after ‘clients’?

Tags:

2

Answers


  1. 0 
    clients.*?([0-9]+)
^^^^^^^            -- exact match
       ^^^         -- non-greedy string of anything
          ^      ^ -- capture
           ^^^^^^  -- string of 1 or more digits (greedy)
    Login or Signup to reply.
  2. 0

    I did this test on MySQL 8.0.29, but it should work on MySQL 5.x too:

    mysql> set @s1 = '..."type":"temp","typeOther":"","clients":"2","hours":"5",...';

mysql> set @s2 = '..."id\":31457,\"clients\":2,\"cancel\":false\...';

mysql> select trim(leading '\"' from substring_index(@s1, '\"clients\":', -1)) as clients;
+--------------------------+
| clients                  |
+--------------------------+
| 2","hours":"5",... |
+--------------------------+

mysql> select trim(leading '\"' from substring_index(@s2, '\"clients\":', -1)) as clients;
+------------------------+
| clients                |
+------------------------+
| 2,"cancel":false... |
+------------------------+

    Then cast the result as an integer to get rid of the non-numeric part following the number.

    mysql> select cast(trim(leading '\"' from substring_index(@s1, '\"clients\":', -1)) as unsigned) as clients;
+---------+
| clients |
+---------+
|       2 |
+---------+

mysql> select cast(trim(leading '\"' from substring_index(@s2, '\"clients\":', -1)) as unsigned) as clients;
+---------+
| clients |
+---------+
|       2 |
+---------+
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search