Quick Question

DigitalSolution

I am currently working on a project and I had one quick question that I could not figure out.

I am trying to actually pull data out of mySQL by the first letter:

Example:
#-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

I have the query for the letters as :

$query = "SELECT * FROM table WHERE column like '".$start."%'";

Where start is the variable letter : A, B, C etc.

Now I am not sure how exactly to pull data out by numbers.. I really do not want to set multiple query's to :

$query = "SELECT FROM table WHERE column like '1%';
$query = "SELECT FROM table WHERE column like '2%';

Etc.

Is there an easier way to just pull data out of mySQL if it starts with a number, anywhere from 0-9 ? I am sure there is.. Can you please help me.. I have used the OR clause but I have had no success.. I did read few articles on it as well....

Best Regards,
-DigitalSolution

altexis

hmm.... I can't figure out a 100% automated solution but why don't you try this one:

use OR with all the numbers:
$query = "SELECT * FROM table WHERE column like '1%' OR column like '2%' OR column like '3%' OR ..... ";

this will give you a result with any number in the beginning of column
then you can filter the records in code level
(which is much faster than making 10 different queries)

DigitalSolution

Thanks for responding so quickly! I am going to go ahead and try that once more, I did try that previously... But let's see if it works...

DigitalSolution

Working perfectly, thanks.. I was wondering if there was a way that was much much more effective then this? A bit easier as well.. ?

Regards,
-DigitalSolution

diego25

You could do something like this:

WHERE SUBSTRING(column, 0, 1) IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0')

I was trying to find a function similar to PHP's is_digit, but I couldn't find it...so I'm using IN () here...

Diego

altexis

I just realized there is one more way
(probably not as good as diego's)

In the ASCII codes numbers are between the character
/
and
:

So you could make your query like:
$query = "SELECT * FROM table WHERE column >'/' AND column <':'";

altexis

eeehhmm.... let me fix it up a bit more 🙂

$query = "SELECT * FROM table WHERE column >='0' AND column <':'";

DigitalSolution

Thanks altexis.. Both ways work, just had to make few modifications for the query but the general idea works well.. Again thanks diego25 & altexis....

Regards,
-DigitalSolution