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);