Mysql check if string contains numbers

Question

I want to count the number of different magnets that are in my table.

They´re defined by a string of around 20 elements, of which I´m interested in the first five.

All of them have the same pattern:

mgn##

the word mgn followed by two numbers. I have to specify that the fourth and fifth element are integers, as some of the rows have letters after mgn.

I´ve tried different queries so far, just guessing in the air.

mysql> SELECT DISTINCT(SUBSTRING(magnet, 1, 5)) FROM magcom WHERE magnet LIKE '%mgn\d\d%';
mysql> SELECT DISTINCT(SUBSTRING(magnet, 1, 5)) FROM magcom WHERE magnet LIKE '%mgn##%';

These gives the message empty set.

Any help would be apprciated.

GMB · Accepted Answer · 2020-12-16 16:32:15Z

1

Use RLIKE:

WHERE magnet RLIKE 'mgn[0-9]{2}'

If you want the match at the beginning of the string only:

WHERE magnet RLIKE '^mgn[0-9]{2}'

answered Dec 16, 2020 at 16:32

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SharkCaller Over a year ago

Thx. That worked. However, would it be possible to do the same, but taking account that some of them may have just one number? It would be something like '^mgn[0-9]{2}' AND '^mgn[0-9]{1}' I suppose, but then SUBSTRING is misplaced.

Collectives™ on Stack Overflow

Mysql check if string contains numbers

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related