Finding duplicate letters in a string beginning and ending at certain positions in MariaDB?

Question

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

I can find all double letters by

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

and positions by

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

but I do not know how to combine them?

so that the following examples are found:

Liverpool;
Sheffield Central.

but not

Arran.

I have tried

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

SELECT *
FROM cte
WHERE c REGEX '([a-zA-Z])\1{1,3}';

I am aware that MariaDB does not support backreferences such as '\1'.

Answer 1

'^.{3,8}(\w)\1'

Explanation --

^ -- anchored at start
.{3,8} -- match 3 to 8 characters (may not be the exact numbers you need)
(\w) -- (or (\[[:ALPHA:]] or ([a-zA-Z]) -- match 1 letter
\1   -- match the same string that was matched by (...)
(And don't care about what occurs afterward.

For MySQL 8, the backslashes need doubling.

Answer 2

A simple solution would be create a number table only with values from 4 to 10 as follows:

CREATE TABLE Numbers (
nr INT );
  
insert into Numbers values 
(4),(5),(6),(7),(8),(9),(10);  

Then use:

SELECT *
FROM   myTable
WHERE  EXISTS (SELECT *
               FROM   Numbers
               WHERE  nr < LENGTH(col)
               AND SUBSTRING(col, nr, 1) = SUBSTRING(col, nr + 1, 1)
              )   ;

Fiddle

The SUBSTRING(col, nr, 1) = SUBSTRING(col, nr + 1, 1) will only return the rows when there are two consecutive same words. As per the from 4th to 10th part it is restricted by the values on the Numbers table

Answer 3

You can use a positions table and check whether the letter in position p is equal to the that one in p+1:

WITH cte AS (
    SELECT *, SUBSTRING(word, 4, 9) AS c
    FROM words
), positions (p) AS ( values (0),(1),(2),(3),(4),(5),(6),(7) ) 
SELECT word, c, p, p+1
FROM positions p 
JOIN cte c1
    ON nullif(substring(c1.c, p, 1),'') 
     = nullif(substring(c1.c, p+1, 1),'')

I did not concider trailing spaces to be equal which is why I used the nullif function in the join predicate

Fiddle

As @ErgestBasha realized it is not necessary to extract the sub- string. A shorter version would be:

WITH positions (p) AS ( values (4),(5),(6),(7),(8),(9),(10) ) 
SELECT word, p, substring(c1.word, p, 1), p+1, substring(c1.word, p+1, 1)
FROM positions p 
JOIN words c1
    ON nullif(substring(c1.word, p, 1),'') 
     = nullif(substring(c1.word, p+1, 1),'')

Sheffield Central   4   f   5   f
Liverpool           7   o   8   o

Fiddle2