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 '(.)11';

---

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.

---

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 '/(.)11/p'

---

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='';
    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).

链接地址: http://www.djcxy.com/p/54626.html

上一篇: MongoDb：避免过多的磁盘空间

下一篇: 在mysql中相同的字符3次条件