Retrieve only records that start with a number

PHP
1

Hey all

I currently have a query that will retrieve all names from my db, by first letter of its name, however I would like to retrieve the same way but only numbers from 0-9

this is my current query that works for A-Z


<?php

if(isset($_GET['by']) AND !isset($_POST['term']) AND !isset($_GET['cat'])) {
		$max = 25;
		$p = $_GET['p'];
		if(empty($p)) {
		$p = 1;
		}
		$limits = ($p - 1) * $max; 
		$letter = $_GET['by'];
		


$sql="SELECT * FROM names  WHERE  type='latest'   AND title LIKE '" . $letter ."%' order by ID desc LIMIT ".$limits.",".$max."";
2 


SELECT * FROM names  WHERE 
SUBSTR(title, -LENGTH(title), 1) IN ('0','1','2','3','4','5','6','7','8','9')
3

or

SELECT * FROM names WHERE
WHERE title regexp ‘[1]’

  1. 0-9 ↩︎

4

If you have an index defined on the title column, this will be more efficient than the previous two solutions (sorry guys!)


SELECT *
  FROM names
 WHERE type = 'latest'
   AND ( title LIKE '0%'
    OR   title LIKE '1%'
    OR   title LIKE '2%'
    OR   title LIKE '3%'
    OR   title LIKE '4%'
    OR   title LIKE '5%'
    OR   title LIKE '6%'
    OR   title LIKE '7%'
    OR   title LIKE '8%'
    OR   title LIKE '9%' )
5

Another way to use the index without writing so much code:


SELECT *
FROM names
 WHERE type = 'latest'
     AND title >= '0'
     AND title < ':' -- To write more clearly, use "title < char(ascii('9') + 1)"
6

That’s a pretty creative solution, I like it :slight_smile: