MySQL LIKE with range doesn't work

Question

I've got a database table mytable with a column name in Varchar format, and column date with Datetime values. I'd like to count names with certain parameters grouped by date. Here is what I do:

SELECT
    CAST(t.date AS DATE) AS 'date',
    COUNT(*) AS total,
    SUM(LENGTH(LTRIM(RTRIM(t.name))) > 4 
        AND (LOWER(t.name) LIKE '%[a-z]%')) AS 'n'
FROM
    mytable t
GROUP BY 
    CAST(t.date AS DATE)

It seems that there's something wrong with range syntax here, if I just do LIKE 'a%' it does count properly all the fields starting with 'a'. However, the query above returns 0 for n, although should count all the fields containing at least one letter.

Answer 1

You write:

It seems that there's something wrong with range syntax here

Indeed so. MySQL's LIKE operator (and SQL generally) does not support range notation, merely simple wildcards.

Try MySQL's nonstandard RLIKE (a.k.a. REGEXP), for fuller-featured pattern matching.

Answer 2

I believe LIKE is just for searching for parts of a string, but it sounds like you want to implement a regular expression to search for a range.

In that case, use REGEXP instead. For example (simplified):

SELECT * FROM mytable WHERE name REGEXP "[a-z]"

Your current query is looking for a string of literally "[a-z]".

Updated:

SELECT
CAST(t.date AS DATE) AS 'date',
COUNT(*) AS total,
SUM(LENGTH(LTRIM(RTRIM(t.name))) > 4 
    AND (LOWER(t.name) REGEXP '%[a-z]%')) AS 'n'
FROM
mytable t
GROUP BY 
CAST(t.date AS DATE)