REGEXP to check for double letters at certain positions in mariadb?

user_11028689 0
Viewed 38

I need to find all strings containing double letters in positions from 4th to 10th inclusive.

I can find all double letters by

'([a-zA-Z])\1{1,3}';

and positions by

SELECT SUBSTRING(columnmame, 4, 9 ) FROM table;

but I do not know how to combine them?

so that the following examples are found:

Liverpool;
Sheffield Central.

but not

Arran.

thank you.

mariadb regex string
1 Answers

This is tricky on MariaDB, because it does not support backreferences such as \1 etc. One trick we can do is to do use a LIKE expression on a substring of the column from positions 4 to 10 inclusive.

WITH cte AS (
    SELECT *, SUBSTRING(columnmame, 4, 9) AS c
    FROM yourTable
)

SELECT *
FROM cte
WHERE c LIKE '%aa%' OR c LIKE '%bb%' OR c LIKE '%dd%' OR
      -- rest of alphabet here
      c LIKE '%xx%' OR c LIKE '%yy%' OR c LIKE '%zz%';
user_18632290
Related