[MySQL] LIKE query

[snapbackz]

SELECT *
FROM `friends_test`
WHERE `friend1_id` = '10'
AND `friend2_name` LIKE 'a%'
LIMIT 10;

I'm using a query like the one above to pull a user's friends that begins with any letter a-z. Some users have names that look like "♥ Liz ♥".

How can we query mysql to pull any name that does NOT begin with a-z?

[Bjom]

SELECT FROM tUser WHERE UserName REGEXP '^[a-zA-Z0-9].'

With REGEXP or RLIKE

SELECT *
FROM `friends_test`
WHERE `friend1_id` = '10'
AND `friend2_name` REGEXP '^[^a-zA-Z0-9].*'
LIMIT 10; 

REGEXP '^{[a-zA-Z0-9].*'}

^=beginning of string
[^] NOT
[^a-zA-Z0-9] NOT any of these: a-zA-Z0-9
.* any number of any character allowed after the first char.

Bjom

[dagon]

I suggest you check when they add a name its not something stupid and starts with a-z

[anzu_m]

you can try this

<?php
$a_z = range('A','Z');
$query = "SELECT * FROM friends_test WHERE friend1_id = '10'
AND upper(friend2_name) ";

foreach ($a_z as $letter)
{
$query .= "NOT LIKE '". $letter . "%'";
if ($letter != 'Z')
$query .= ' AND upper(friend2_name) ';
}
$query .= " ORDER BY friend2_name ASC"
?>