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.
