CODEWITHSUNDEEP
phpmysql
Brandon
Brandon

Reputation: 2171

Any way to select from MySQL table where a field ends in certain character/number?

I am hoping to run a mysql_query where I can select a row if a certain field ends in a number. For example:

<?php
$query = mysql_query("SELECT id FROM table WHERE [ID ENDS IN 0]");
$query = mysql_query("SELECT id FROM table WHERE [ID ENDS IN 1]");
$query = mysql_query("SELECT id FROM table WHERE [ID ENDS IN 2]");
$query = mysql_query("SELECT id FROM table WHERE [ID ENDS IN 3]");
//and so on...
?>

Is there a way to do this? Any help would be great. Thank you!

Upvotes: 2

Views: 7928

Answers (7)

Elaine
Elaine

Reputation: 1

SELECT id FROM table WHERE id REGEXP '1[[:>:]]'

Upvotes: 0

Pablo Jomer
Pablo Jomer

Reputation: 10378

You can use LIKE and a wild card expression it should be somthing like 

SELECT id FROM table WHERE id REGEX '%0$'
SELECT id FROM table WHERE id REGEX '%1$'

and so on.

Upvotes: 0

jsist
jsist

Reputation: 5253

You can use regular expressions if you need to find if field is ending in a number or not as follows

SELECT id FROM table WHERE id REGEXP '[0-9]$'

Hope it helps...

Upvotes: 1

x4rf41
x4rf41

Reputation: 5337

SELECT id
  FROM table
 WHERE id LIKE '%0'

Upvotes: 1

GordonM
GordonM

Reputation: 31730

You could do it with a cast and LIKE, but the performance is likely to be terrible for any non-trivial amount of data (I've not tested in your particular case, but in my experience, casting to string so you can use string operations really slows a query down).

A better way would be to use modulus.

For example, to get all the rows where the numerical field ends in a 4: 

SELECT *
FROM table
WHERE MOD(column_of_interest, 10) = 4

Again, I've not benchmarked this, but it's probably going to perform better than casting would.

Of course, if the column type is already a string, then LIKE is the way to go, as using MOD on strings would also require a cast.

Upvotes: 0

Surace
Surace

Reputation: 711

SELECT id FROM table WHERE mod(id, 10) = 1

give it a go

Upvotes: 2

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 798546

SELECT ...
  WHERE somefield LIKE '%1'

Upvotes: 3

Related Questions