How do you query rows with regex that includes column values

Question

I've got a table where identifiers(XXXXX-065-00) should match a division(65). I'd like to query all the rows that don't match.

The following SQL seems to work, but is there a better or more efficient way of doing this?

select id,division,identifier from table where identifier not REGEXP '.....-'+division+'-..'

Are the division numbers all the same length? And if they are shorter than 3 digits, should the preceding digit(s) be part of the match? — Nick
– Nick, Commented Feb 6, 2020 at 23:10
The division can be any number between 10 and 175, but they are represented in the identifier as 010 - 175 ( the identifier always has the same number of characters. ) — HLD
– HLD, Commented Feb 7, 2020 at 23:31

GMB · Accepted Answer · 2020-02-07 23:48:00Z

1

You can concat() the regex string:

where identifier not regexp concat('^.{5}-', lpad(division, 3, '0'), '-..$')

Note that in MySQL + is a numeric addition (it does not do string concatenation, like in SQL Server for example).

Other remarks:

you probably need to pad the division with '0's so it has exactly 3 characters
I added ^/$ to make the regex match on the entire string rather than do partial matching, since this seems to be what you want
you can use quantifiers: .{5} stands for .....

edited Feb 7, 2020 at 23:48

answered Feb 6, 2020 at 21:48

GMB

224k25 gold badges103 silver badges151 bronze badges

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

3 Comments

HLD Over a year ago

Is there a way to "loosely" pad the division in the event that it's 150 instead of 050?

GMB Over a year ago

@HLD: lpad(division, 3, '1')?

HLD Over a year ago

where identifier not regexp concat('^.{5}-', lpad(division, 3, '0'), '-..$') did the trick. I think the + in the last part of the concat is redundant.

Collectives™ on Stack Overflow

How do you query rows with regex that includes column values

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related