Best way to select values ​​starting with characters

Question

I have a category (title) starting with alphabetical A

$sql = "select * from category_data WHERE category LIKE 'A%'"; 

And it works, but how can I select from the database category ( title ) that do not start with [AZ] or [1-9] with only characters

Answer 1

 SELECT * FROM category_data WHERE ASCII(UPPER(LEFT(category,1))) IN(...) 

You can pass allowed ASCII characters in your IN clause.

If you want to skip a specific range, you can use NOT BETWEEN x AND y;

Answer 2

You can simply use the REGEXP function of MySQL as

 SELECT * FROM `category_data` WHERE `category` REGEXP '^[^a-zA-Z0-9]' 

Answer 3

You can simply check to see if there is a 1st character in the alphabet or number, for example:

 $sql = "select * from category_data WHERE substr(category,1) NOT LIKE '%[^a-zA-Z]%' AND substr(category,1) NOT LIKE '%[^0-9]%'"; 

Answer 4

Use REGEX

  $sql = "select * from category_data WHERE category NOT REGEXP '^[^A-Z0-9]'"; 

This will give you a category name that does not start with AZ and 0-9

Answer 5

 SELECT * FROM category_data WHERE LEFT( category, 1 ) NOT IN ('a','b','c') 

// add the rest of char to the IN list

Answer 6

Try this if you do not want to use regex

 SELECT * FROM category_data WHERE (LEFT(category, 1) NOT BETWEEN 'a' AND 'Z') AND (LEFT(category, 1) NOT BETWEEN 0 AND 9); 

LEFT(category, 1) captures the first character of your category column and confirms that it is not between az or 0-9 .

Answer 7

I tried a couple of solutions and the regex was the slowest.

The quickest solution is to compare the first character with whitelist characters using the IN (...) clause:

 SELECT * FROM category_data WHERE LEFT(category, 1) NOT IN( 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' ); 

The second quickest solution is to use the INSTR function to check the first character against the whitelist:

 SELECT * FROM category_data WHERE INSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', LEFT(category, 1)) = 0;