B.Termeer B.Termeer - 16 days ago 8
SQL Question

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?

Answer

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 .