CODEWITHSUNDEEP
mysqlsqlregex
Sergio Serro
Sergio Serro

Reputation: 13

MySQL REGEXP 3 times repeating characters

I want to find string in mysql with 3 repeating character. I read all answers like here Regex to find repeating numbers but it does not work with MySQL 5.7:

select  '211' REGEXP '(.)\1\1'; //true but I expect false

select  '211' REGEXP '(.)\1{2}'; // true but I expect false

select  '211' REGEXP '([[:alnum:]])\1\1'; //true but I expect false

select  '211' REGEXP '(\w)\1{2}'; //false but

select  '111' REGEXP '(\w)\1{2}'; // also false

UPDATE: As tested it works as expected with MySQL 8.

Upvotes: 1

Views: 658

Answers (2)

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30585

Try This:

select  '111' REGEXP '(\\w)\\1\\1';

Working Fiddle

This query is Tested on Mysql 8 and Maria DB 10

For MySql 5.7 And Lower, I ended up with dirty workaround

select  
 CASE WHEN (
  @X:=SUBSTRING(C,1,1)) = @X and 
  @X=SUBSTRING(C,2,1) AND 
  @x=SUBSTRING(C,3,1)
 THEN 
  1 
 ELSE 
  0 
 END MyMatch
FROM
  (SELECT '111' c) T

Upvotes: 0

GMB
GMB

Reputation: 222502

That could have been :

(.)\1{2}

Unfortunately, MySQL regexes do not support backreferences, so this will not work.

I guess that you would have to manually define all supported combinations and explicitly put them in the regex, like :

(000)|(111)|(222)|...|(999)|(aaa)|(bbb)|...|(zzz)

(you need to replace the ... with all other patterns)

If you want to identify strings that contain only 3 similar characters and nothing else, add « ^ » at the beginning of the regex and « $ » at the end.

Upvotes: 1

Related Questions