MySQL query with regular expression

Question

I have a MySQL table with some addresses on it. I would like to find the addresses that contain double letters between the 5th and 11th position. There is a test table with some data for reference.

CREATE TABLE test(
address VARCHAR(500));

INSERT INTO test (address)
VALUES ('Western Marrow'),
('Kennffort Plains'),
('Oaksbrook Terrace'),
('Southern Old Saybrook')

The last record 'Southern Old Saybrook' should not appear because its double letter is not within the 5th and 11th position. Now, I have used this query to get the double letters:

SELECT address
from test
WHERE address REGEXP 'a{2}|b{2}|c{2}|d{2}|e{2}|f{2}|g{2}|h{2}|i{2}|j{2}|k{2}|l{2}|m{2}|n{2}|o{2}|p{2}|q{2}|r{2}|s{2}|t{2}|u{2}|v{2}|w{2}|x{2}|y{2}|z{2}';

But I am failing to conduct the search within the given range. How could I modify the query to apply the regular expression within the range?

What about the third sentence? 'Oaksbrook Terrace' Should match? — Alireza
– Alireza, Commented Aug 18, 2021 at 19:38
Yes it should match because the letter contains two o within the range. — Kintaro
– Kintaro, Commented Aug 18, 2021 at 20:38

Barmar · Accepted Answer · 2021-08-18 19:39:07Z

3

Use SUBSTR() to get the substring between 5th and 11th position.

WHERE SUBSTR(address, 5, 6) REGEXP '...'

answered Aug 18, 2021 at 19:39

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

2 Comments

WOUNDEDStevenJones Over a year ago

dev.mysql.com/doc/refman/8.0/en/… for reference

Kintaro Over a year ago

Thank you. it works! I mark this as solved.

Rick James · Accepted Answer · 2021-08-19 00:08:23Z

1

Much shorter than the hastily accepted answer:

WHERE SUBSTR(address, 5, 6) REGEXP '([a-z])\\1'

Caveat: Only checked on MySQL 8.0.

Caveat: Case folding or not is determined by collation of the string.

edited Aug 19, 2021 at 0:08

answered Aug 19, 2021 at 0:03

Rick James

144k15 gold badges144 silver badges255 bronze badges

2 Comments

Kintaro Over a year ago

Much shorter code. Thank you. But I don't know what the \\1 means. I will look into it.

Rick James Over a year ago

\\1 means to use what was found by the first ("1") match ((...)). In your case -- same letter twice.

Collectives™ on Stack Overflow

MySQL query with regular expression

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related