mariaDB extract only the digits from {"user":"128"}-CodePudding

From the string {"user":"128"}, expected only 128.

Could also be {"user":"8"} or {"user":"12"} or {"user":"128798"}

In this fiddle example the matched value should be 3

Schema:

CREATE TABLE test1 (
  id INT AUTO_INCREMENT,
  userid INT(11),
  PRIMARY KEY (id)
);

INSERT INTO test1(userid)
VALUES
('126'),
('2457'),
('3'),
('40');

CREATE TABLE test2 (
  id INT AUTO_INCREMENT,
  code VARCHAR(1024),
  PRIMARY KEY (id)
);

INSERT INTO test2(code)
VALUES
('{"user":"128"}'),
('{"user":"2459"}'),
('{"user":"3"}'),
('{"user":"46"}');

Query:

(SELECT sub.`userid`, rev.`code` REGEXP '[0-9]'
FROM `test1` AS sub, `test2` AS rev
WHERE sub.`userid`= rev.`code` 
)

Have tried REGEXP '[0-9]' and also '[[:digit:]]' but no luck.

I have also tried concat ('',value * 1) = value or concat(value * 1) = value

SOLUTION: fiddle

CodePudding user response：

REGEXP will return 0 or 1 if the regexp matches the string.

You should have a look to 'regexp_substr' https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-substr

select REGEXP_SUBSTR(code, '"[0-9] "') from test2

This works for me, after selecting MySQL 8 version in your fiddler. it returns :

"128"
"2459"
null
"46"

select REGEXP_SUBSTR(code, '[0-9] ') from test2

if you want 3 instead of null for "k3"

akina suggestion of using JSON_EXTRACT is way better, but does require that you redefine the column as a JSON datatype from VARCHAR.

CodePudding user response：

Use the function REGEXP_SUBSTR:

MariaDB [(none)]> SELECT  REGEXP_SUBSTR('{"user":"128"}', '\\d ');
 ----------------------------------------- 
| REGEXP_SUBSTR('{"user":"128"}', '\\d ') |
 ----------------------------------------- 
| 128                                     |
 ----------------------------------------- 
1 row in set (0.000 sec)