Query the user name of the double letter-CodePudding

I want to find usernames with reduplicated words in my database

Below is my SQL statement：

SELECT * FROM users WHERE BINARY `name` regexp 'a{2}|b{2}|c{2}|d{2}|e{2}|f{2}|g{2}|h{2}|i{2}|j{2}|k{2}|l{2}|m{2}|n{2}|o{2}|p{2}|q{2}|r{2}|s{2}|t{2}|u{2}|v{2}|w{2}|x{2}|y{2}|z{2}'

I guess there should be an easier way, but I don't know how to do it.

I also want to find usernames like 'ABAA' or 'AABA', but I won't write this SQL or say this regex

CodePudding user response：

As MySQL's regex engine does not support backreferences, your approach is reasonable. To simplify your query, you could maintain a table of double letters:

table: letters (val)
aa
bb
...
zz

Now your query can be simplified to:

SELECT *
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM letters l
    WHERS INSTR(u.name, l.val) > 0
);

CodePudding user response：

Try:

create table users(name varchar(100));
insert into users (name) values
       ("abaa"),
       ("bba"),
       ("abc"),
       ("aCCa");

SELECT * FROM users WHERE LOWER(name) REGEXP 'aa|bb|cc|dd|ee|ff|gg|hh|ii|jj|kk|ll|mm|nn|oo|pp|qq|rr|ss|tt|uu|vv|ww|xx|yy|zz';

Prints:

abaa
bba
aCCa

CodePudding user response：

i think i found the answer,I upgraded my MySQL database to MariaDB.

this is my sql statement:

SELECT * FROM users WHERE `name` REGEXP '([a-z])\\1'

This is the sql statement to query like 'aaba':

SELECT * FROM users WHERE `name` REGEXP '([a-z])\\1((?!\\1)[a-z])\\1'

Note: Because MariaDB uses the C escape syntax in strings (for example, "\n" to represent the newline character), you must double any "" that you use in your REGEXP strings.