Stonesmit Stonesmit - 4 months ago 7
SQL Question

Mysql find records with double letters in them

Guys I need a query which list me all those records in which there are at least 2 of the same letter. Unfortunately

"REGEXP '[a]|[b]|[c]|[d]|[e]|[f]|[g]|[h]|[i]|[j]|[k]|[l]|[m]|[n]|[o]|[p]|[q]|[r]|[s]|[t]|[u]|[v]|[w]|[x]|[y]|[z]';"


this one does not work for me, neither the same expession with
[a]{2}
attributes.

Any suggestions?
Thanks for the help

Answer

You can use this query:

SELECT some_fields FROM some_table WHERE field 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}';

It will return all the records in some_table whose field contains two identical characters side-by-side.

Examples of what it could return (based on one of my local MySQL tables):
hobbit
Abba
Isaac
scrabble

In case the characters don't have to be together, you may use this other query:

SELECT some_fields FROM some_table WHERE field REGEXP
'a.*a|b.*b|c.*c|d.*d|e.*e|f.*f|g.*g|h.*h|i.*i|j.*j|k.*k|l.*l|m.*m|n.*n|o.*o|p.*p|q.*q|r.*r|s.*s|t.*t|u.*u|v.*v|w.*w|x.*x|y.*y|z.*z';

Examples of what it could return (based on one of my local MySQL tables):
probably (double B)
curious (double U)
contrary (double R)
Abba (double A and double B)

Documentation: MySQL pattern matching

Comments