MySQL – SELECT Rows Where First Character Is Not A Letter

MySQL – SELECT Rows Where First Character Is Not A Letter

Here are two methods of SELECTING rows where the first character is not a letter. For example, if you have the following data:

So let’s say you want to select the row with the id 21 because the first_name value doesn’t start with a letter, it’s starts with a zero.
There are actually two methods of achieving this; most obvious being the good old REGEXP solution.

Method 1- Using REGEXP

1	SELECT * FROM table WHERE first_name REGEXP '^[^A-Za-z]';

That will SELECT all rows where the first_name field value does NOT start with a letter. While it’s probably the most obvious and common solution used, it’s not the most efficient. Method 2 is much more efficient.

Method 2 – Using > and < comparators (the better solution)

1	SELECT * FROM users WHERE first_name <= '@' or first_name >= '{'

Using > and < comparators is much more efficient than REGEXP, so this is the better solution in my opinion. To further improve performance, it’s probably a good idea to index the field you’re running this query on. In this example, I would apply an index to the “first_name” field.
Please let me know if this query works for you, or if you can think of an even better solution to the problem.