CODEWITHSUNDEEP
mysqlregex
barroco
barroco

Reputation: 3108

Same character 3 times condition in mysql

I want a query (in msyql) to get records with same character 3 times:

aa => false
aaa => true
baaa => true
aaab => true
aaaaaab => true
baaab => true
babababa => false

For any character, not only 'a' and 'b'.

The following sentence doesn't work:

SELECT field1
FROM mytable
WHERE field1 REGEXP '(.)\1\1';

Upvotes: 8

Views: 2582

Answers (3)

Kusalananda
Kusalananda

Reputation: 15613

Because of the limitation of regular expressions in MySQL, I don't think there's a simple way of doing this entirely in the database. Maybe it would be easier to do it as a post-processing step. Here's a suggestion using sed on the command line:

mysql -BN {connection parameters and query} | sed -n '/\(.\)\1\1/p'

Upvotes: 1

Nishu Tayal
Nishu Tayal

Reputation: 20840

May be this can help you. 

select * from test1 where name REGEXP  '[a]{3}|[b]{3}|[c]{3}|[d]{3}|[e]{3}|[f]{3}|[g]{3}|[h]{3}|[i]{3}|[j]{3}|[k]{3}|[l]{3}|[m]{3}|[n]{3}|[o]{3}|[p]{3}|[q]{3}|[r]{3}|[s]{3}|[t]{3}|[u]{3}|[v]{3}|[w]{3}|[x]{3}|[y]{3}|[z]{3}';

It returns records for 3 same and consecutive letters.

Upvotes: 3

Doug Kress
Doug Kress

Reputation: 3537

I know this isn't what you were looking for, but I had fun with it anyway. Polish off my function building skills.

drop function if exists char_repeats;
DELIMITER //

CREATE FUNCTION char_repeats(string varchar(255), repeat_count int)
    RETURNS int DETERMINISTIC
BEGIN
    DECLARE pos int;
    DECLARE len int;
    DECLARE last_char CHAR(1);
    DECLARE cnt int;
    DECLARE ch CHAR(1);

    SET pos = 0;
    SET cnt = 1;
    SET last_char='\0';
    SET len = length(string);


    REPEAT
        SET ch = substring(string, pos, 1);
        IF last_char = ch THEN
           SET cnt = cnt + 1;
        ELSE
           SET cnt = 1;
           SET last_char = ch;
        END IF;

        SET pos = pos + 1;
    UNTIL pos = len OR cnt = repeat_count
    END REPEAT;
    RETURN cnt = repeat_count;
END
//

DELIMITER ;

It's not that slow - I ran it across a ~270,000 record database against a field with an average length of 17 characters in 25 seconds (on my local MBP).

Upvotes: 0

Related Questions