How to query a table column for values that start with a non-alphabetic character?

Question

How can rows starting with non alphabetic characters (all "special" characters, including spaces and underscores) be selected?

I don't know if it changes anything, but my_table will also contain values starting with Greek letters.

$query = $db->getQuery(true);
$query->clear();
$query
    ->select(array('id', 'last_name', 'first_name'))
    ->from($db->qn('#__my_table'))
    ->where($db->qn('last_name').' LIKE '. $db->q('[^a-zA-Z%]'))
    ->order('last_name, first_name ASC')
    ->setLimit($startrow, '100');
$db->setQuery($query);

The query form is correct and will return the correct results if I set it as: $db->q('A%') so my mistake must be with the regex, but I can't see where?

EDIT AFTER QUESTION

I am trying to create a letter menu like this A B C D...Α Β Γ Δ...#. When you click a letter it shows all lastnames starting from that letter when you click the # it should return all other "lastnames" (users imports the lastnames so it could be any special character or number, space, underscores).

This code is for when clicking # so it should filter out all lastnames starting with English and all lastnames starting with Greek characters. If there is any other way to categorize what I need please advice.

I found NOT REGEXP '. $db->q('[A-Za-z%]')) (UPDATED CODE BELOW) that will do most of the job, but it won't filter the Greek letters.

$query = $db->getQuery(true);
    $query->clear();
    $query
        ->select(array('id', 'last_name', 'first_name'))
        ->from($db->qn('#__my_table'))
        ->where($db->qn('last_name').' NOT REGEXP '. $db->q('[A-Za-zα-ωΑ-Ω%]'))
        ->order('last_name, first_name ASC')
        ->setLimit($startrow, '100');
    $db->setQuery($query);

SOLVED

I was able to find the correct regex

$query = $db->getQuery(true);
$query->clear();
$query
    ->select(array('id', 'last_name', 'first_name'))
    ->from($db->qn('#__my_table'))
    ->where($db->qn('last_name').' NOT REGEXP '. $db->q('^[a-z|α-ω]'))
    ->order('last_name, first_name ASC')
    ->setLimit($startrow, '100');
$db->setQuery($query);

Answer 1

First of all, do you actually need a where clause? Are you really trying to exclude any names? What are the actual exclusions you want?

If you don't mean to filter any names, then you can simply remove the WHERE clause in your query and have all the users/usernames.

Secondly, you can't pass such regular expressions in LIKE. I bet your above query isn't returning anything at all. That is because your WHERE is searching for names that start with [^a-zA-Z] and after that can have anything.

In contrast the LIKE 'A%' is searching for all names that start with A and have anything after that.

If you need to do regex, then MySQL provides REGEXP & NOT REGEXP & RLIKE.

MySQL Documentation:

• Pattern Matching

• Regular Expressions

It's a bit vague what you are trying to filter... Is it names that start with numbers, or what?

If so, this is a sample MySQL query with regex to filter out those:

$query
    ->select(array('id', 'username'))
    ->from($db->qn('#__users'))
    ->where($db->qn('username').' NOT REGEXP '. $db->q('^[0-9]'))
    ->order('username ASC')
    ->setLimit($startrow, '100');

Answer 2

I would like to refine the OP's solution edited into the question. So that researchers can compare and understand what is happening.

$query = $db->getQuery(true)
    ->select($db->qn(["id", "last_name", "first_name"])
    ->from($db->qn("#__my_table"))
    ->where($db->qn("last_name") . " REGEXP " . $db->q("^[^a-zα-ω]"))
    ->order($db->qn(["last_name", "first_name"]))
    ->setLimit((int)$limit, (int)$offset);
$db->setQuery($query);

• I don't know that you need clear() so I'm removing it.
• I have added all of the unnecessary qn() calls that Joomla Standards demands.
• Your regex logic seeks rows that do not start with your two ranges of letters. To write a "negated character class, use ^ just after the [. Inside of a character class you do not use pipes to separate character ranges, just put them side by side.
• By using a negated character class, you can drop the NOT from before REGEXP.
• The default sorting direction is ASC, so you can omit that declaration from your query.
• You have your start and limit parameters reversed. The limit should come before the offset. https://api.joomla.org/cms-3/classes/JDatabaseQueryLimitable.html#method_setLimit