Enquiry Email Form
Home » Web development notes » MySQL Functions, Tips & Tricks » 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:

MySQL - SELECT Row Where First Character Is Not A Letter

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.

Learning & sharing

This is where we store some of our Web Development thoughts, tips and tricks for both our own uses and for others to learn from. Sharing is caring.

Please feel free to contribute to our blog posts; perhaps even teach us a few tricks of your own. We'd love to hear your thoughts.



X
MENU