CODEWITHSUNDEEP
sqlms-access
B.Termeer
B.Termeer

Reputation: 315

Access SQL: like function on a number field

I have ,for example, this table in a Microsoft Access database:

id                        numeric
context                   text
numberfield               numeric

I want to select every record that ends with 9 in the column"numberfield". This gives a problem because it is a numeric field and as a result I can not use the following SQL:

select * from table where numberfield like "%9"

A solution is that I change the numberfield to a text. But this gives a problem because there are several users and the change might give a problem in the future. Is there an option to select on the ending when it is a number field?

Upvotes: 2

Views: 2295

Answers (3)

sagi
sagi

Reputation: 40481

That sound a little fishy.. are you sure you can use that query? Don't know about Access but almost any other DBMS allows it.

If it really doesn't work, you can do this:

select * from table where STR(numberfield) like "*9"

EDIT: Maybe it didn't work because you used % which is used with * in Access :

select * from table where numberfield like "*9"

Upvotes: 2

Lybren
Lybren

Reputation: 330

Instead of casting to string and comparing, just extract the rightmost digit with a MOD operation.
Edit your query as follows: 

SELECT *
FROM table 
WHERE ((([numberfield] Mod 10)=9));

Upvotes: 0

Gustav
Gustav

Reputation: 55841

Numbers are numbers, so use Mod for this:

select * from table where numberfield mod 10 = 9

Upvotes: 1

Related Questions