1

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
Sergio Serro
  • 13
  • 3

2 Answers2

1

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • SELECT '111' REGEXP '([[:digit:]])\1\1'; works well. MySQL supports backreferences sometimes – Sergio Serro Jan 28 '19 at 20:06
  • @SergioSerro : ok, then does « (.)\1{2} » work ? Also, which version of MySQL do you use ? – GMB Jan 28 '19 at 20:08
  • No, it does not (for mysql 5.7). Try ```SELECT '211' REGEXP '([[:digit:]])\1\1'```; For MySQL \1 is just 1 – fifonik Feb 28 '19 at 22:27
0

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
Derviş Kayımbaşıoğlu
  • 28,492
  • 4
  • 50
  • 72