Fields starting with a number, etc

[nobertos]

Hi,

I have a large table with a name field in it. I have a query that will return all names that start with a regular alphabetic character (a-z).

1. I need a query that will return all names that start with a number, like:

1st Choice Supplies

2. I also need a query that will return all names that start with anything other than a number or a regular (a-z) character, like:

"G FORCE" Systems

Thanks,
CG

[YappyDog]

"I have a query that will return all names that start with a regular alphabetic character (a-z). "

What query are you using to do this?

[nobertos]

A $letter variable is passed to my page in some cases. It is just a regular ol a-z character.

$alpha_query = "SELECT user_id, name FROM mail_list WHERE name LIKE '$letter%' ORDER BY name ASC";

I am trying to figure out a query that will match everything else (non alphabetical character), so I tried:

$other_query = "SELECT user_id, name FROM mail_list WHERE left(name, 1) REGEXP "!^[a-zA-Z]" ORDER BY name ASC";

but i get a parse error

CG

[YappyDog]

Have you tried backslashing the quotes within your query?

$other_query = "SELECT user_id, name FROM mail_list WHERE left(name, 1) REGEXP \"!^[a-zA-Z]\" ORDER BY name ASC";

[nobertos]

now i do it like this (single quotes), but it doesn't return anything...

$other_query = "SELECT user_id, name FROM mail_list WHERE left(name, 1) REGEXP '!^[a-zA-Z]' ORDER BY name ASC";

[Lars_Berg]

To get records beginning with a digit

where substring(name from 1 for 1) 
between '0' and '9'

To get records not beginning with a digit
nor with a to z

where substring(name from 1 for 1) 
not between '0' and '9'
and substring(name from 1 for 1) 
not between 'a' and 'z'