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

MySQL regexp returns wrong result when working with repeated character

With MySQL 5.7, I have the following query:

SELECT `slug` FROM `unittest`.`test_item`
WHERE `slug` REGEXP '^some-slug[0-9]*$'
ORDER BY `slug` DESC LIMIT 1;

That works just fine for the items with the slugs some-slug1 to some-slug9, but once I have a slug like some-slug10, that one won’t be found. Instead, the result is some-slug9 all the time.
I’ve set up an example at regex101.com, where it’s seems to work.

What am I missing here regarding MySQL?

Tags:

2

Answers


  1. 0

    Another option:

    SELECT 
  `slug`,
  REGEXP_REPLACE(`slug`,'[a-z]','')
FROM `test_item`
WHERE `slug` REGEXP '^some-slug[0-9]*$'
ORDER BY CAST(REGEXP_REPLACE(`slug`,'[a-z]','') as SIGNED)

    see: DBFIDDLE

    EDIT: As a bonus (just to learn)

    Why is this also order correctly?

    SELECT 
  `slug`,
  REGEXP_REPLACE(`slug`,'[a-z]','')
FROM `test_item`
WHERE `slug` REGEXP '^some-slug[0-9]*$'
ORDER BY CAST(REGEXP_REPLACE(`slug`,'[a-z]','') as UNSIGNED)

    see: DBFIDDLE

    HINT: Just add CAST(REGEXP_REPLACE(slug,'[a-z]','') as UNSIGNED) to your select, and you will see why.

    Login or Signup to reply.
  2. 0

    If you change your query to:

    SELECT `slug`
FROM `unittest`.`test_item`
WHERE `slug` REGEXP '^some-slug[0-9]*$'
ORDER BY SUBSTRING(`slug`, 10) + 0 DESC
LIMIT 1;

    SUBSTRING(`slug`, 10) will return numeric component as a string and then +0 causes an implicit conversion to a numeric value (DOUBLE according to Workbench).

    You could also:

    ORDER BY CAST(SUBSTRING(`slug`, 10) AS UNSIGNED) DESC

    to explicitly CAST() the numeric component to an UNSIGNED BIGINT.

    Here’s a db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search