MYSQL - how to extract the number values from string?

Question

I got string values within the database that might have a format like :

hdpos1234x93m4s9034z1
ryhp5x908m4s1s

Unfortunately, I don't have MySQL version 8 to use the REGEX_SUBSTR function. I'm trying to get the digital values before and after the "x".

So I want these values. Any insight would be great.

1234 93
5 908

you can try one of these functions https://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql — nbk, Mar 06 '20 at 22:00

score 2 · Answer 1 · answered Mar 06 '20 at 22:21

Well clearly you can read the columns using some hosted language such as Python or PHP and then use its regex support to do what you want. To limit the rows you need to read, you can use the RLIKE function (I know it's available in MySQL 5.6):

SELECT x FROM mytable WHERE x RLIKE '[0-9]x[0-9]'

That should select rows that contain two numbers separated by an x in column x. You can, of course, use a different pattern that is more suitable for your needs.

Sal · Accepted Answer · 2020-03-07T01:52:54.937

Following function walks away from pLocate in pSteps (signed) until pString does not match pRegex:

CREATE FUNCTION regexFromLocate( pString VARCHAR(255)
                               , pRegex  VARCHAR(255)
                               , pLocate VARCHAR(255)
                               , pStep   INT 
                               ) RETURNS VARCHAR(255)
BEGIN
  DECLARE len INT DEFAULT length(pString);
  DECLARE pos INT DEFAULT locate(pLocate,pString)+if(pStep>=0,length(pLocate),0);
  DECLARE i INT DEFAULT 0;
  DECLARE str VARCHAR(255);
  DECLARE res VARCHAR(255);
  REPEAT
     SET res =  str;
     SET i = i+pStep;
     SET str = if( pStep >= 0
                 , substring( pString, pos  ,  i)
                 , substring( pString, pos+i, -i)
                 );
  UNTIL (   ( pStep <  0 and i <= -pos )
         OR ( pStep >= 0 and i >= len  )
         OR str not regexp pRegex
        )
  END REPEAT;
  RETURN res;
END

Example:

mysql> SELECT regexFromLocate(string, '^[0-9]+$', 'x', -1) lNum
    ->      , regexFromLocate(string, '^[0-9]+$', 'x',  1) rNum
    ->   FROM ( SELECT 'hdpos1234x93m4s9034z1' string
    ->          UNION 
    ->          SELECT 'ryhp5x908m4s1s'
    ->        ) t;
+------+------+
| lNum | rNum |
+------+------+
| 1234 | 93   |
| 5    | 908  |
+------+------+
2 rows in set (0.00 sec)

Thank you! This works so great! Looks like I would need a customized function to extract the values. — Gordon, Mar 09 '20 at 13:50

MYSQL - how to extract the number values from string?

2 Answers2