user3100444 user3100444 - 1 year ago 122
SQL Question

SQL Server : How to test if a string has only digit characters

I am working in SQL Server 2008. I am trying to test whether a string (varchar) has only digit characters (0-9). I know that the IS_NUMERIC function can give spurious results. (My data can possibly have $ signs, which should not pass the test.) So, I'm avoiding that function.

I already have a test to see if a string has any non-digit characters, i.e.,

some_column LIKE '%[^0123456789]%'

I would think that the only-digits test would be something similar, but I'm drawing a blank. Any ideas?

Answer Source

Use Not Like

where some_column NOT LIKE '%[^0-9]%'


declare @str varchar(50)='50'--'asdarew345'

select 1 where @str NOT LIKE '%[^0-9]%'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download