We help IT Professionals succeed at work.

SQL: Check a String for Non-numeric Value

dev775
dev775 asked
on
10,122 Views
Last Modified: 2008-11-26
Looking for the most efficient SQL query to return rows that contain any non-numeric value in a specific field.  The field is of datatype varchar, but I must identify any entries that contain a non-numeric character.
Comment
Watch Question

Commented:
select * from yourtablename
where isnumeric(fieldtocheck)=0
Dmitry GSenior Developer
CERTIFIED EXPERT

Commented:
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
CERTIFIED EXPERT
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks to all for the quick response.  The SQL from acperkins did the job perfectly.  appari, you script only returned records where the field contained entirely non-numeric characters... if for example, the value was A4, then this script did not work.
CERTIFIED EXPERT
Top Expert 2012

Commented:
For the most part the ISNUMERIC() function is successful, however if the number is an exponential value such as 123456E12 than ISNUMERIC() will return true.  This may not always be desired.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.