Pattern Matching using REGEXP in MySQL

Question

I'm trying to make a column in mysql called group if from the second through the third position of the string matches exactly 07. For instance, the string a07ha should be categorized as 07 since this satisfies the condition. This gives me an error. I guess I'm messed up with the = '07' part. Any advice would be of great help.

SELECT
    CASE WHEN 'a07ha' REGEXP '^{2,3}' = '07' THEN '07'
    ELSE '00'
END AS group

I would suggest that it would be better to make a group field that contains OLNY the group code and nothing else — RiggsFolly
– RiggsFolly, Commented Mar 7, 2022 at 18:09
REGEXP doesn't return the matching part, it just returns TRUE or FALSE. — Barmar
– Barmar, Commented Mar 7, 2022 at 18:11
Also, your regexp doesn't match characters 2-3. It returns the last 2-3 characters because the $ matches the end of the string. — Barmar
– Barmar, Commented Mar 7, 2022 at 18:13
You can't use {2,3} without a pattern before it. It means 2-3 repetitions of the preceding pattern. — Barmar
– Barmar, Commented Mar 7, 2022 at 18:19

Bill Karwin · Accepted Answer · 2022-03-07 18:17:13Z

1

You described your condition:

if from the second through the third position of the string matches exactly 07.

The {2,3} pattern does not need to be used for this. All you need to check that the literal characters 07 follow the first character.

SELECT CASE WHEN 'a07ha' REGEXP '^.07' THEN '07' ELSE '00' END AS `group`

answered Mar 7, 2022 at 18:17

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

Collectives™ on Stack Overflow

Pattern Matching using REGEXP in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related