SQL test if field is numeric

guys how do i test if a field value is numeric using an SQL way?

i can simply do isnumeric but that isn't very fast cause it's calling VBA functions.

thanks guys!! = ))
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

How about a regex..?


SELECT * FROM myTable WHERE col1 REGEXP '[0-9]+';

Guy Hengel [angelIII / a3]Billing EngineerCommented:
that is Mysql, the question is MS Access, which doesn't have REGEXP.

as I wrote in your other question (http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28230107.html#a39462672), the isnumeric function used in your SQL is "not the same thing" as in the VBA, though they do the same thing.

and I would indeed recommend to use that one in your SQL, anything else will just be more complex.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
You can try with:

[Field] = Val([Field])

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

developingprogrammerAuthor Commented:
i see cool thanks angelIII and gustav!

hrmm i think all my questions lead to the same solution source - which is where do i get the documentation for all the Access SQL functions? i can find the documentation for T-SQL but not for Access-SQL and i think they're slightly diffferent
developingprogrammerAuthor Commented:
guy the question in my previous comment is pending an conclusion from my other questions as well. once i get the conclusion from the other questions i'll post it here too. thanks!! = ))
developingprogrammerAuthor Commented:
ok guys the conclusion from everyone who's participated in all my other questions linked to this topic is....... TA DA! THERE IS NO SPECIFIC DOCUMENTATION FOR JET SQL FUNCTIONS! so make merry and be happy!! hahaha = ))
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.