SQL Server - how to check if value is not numeric?

Asked By Ric on 22-Jun-07 05:45 PM
Select * from tbl1
where Right(Alpha_Numeric, 5) ... Is not numeric

a value for Alpha_Numeric might be 'abc12345'  or 'abc123mm'

I need to retrieve the row where Alpha_Numeric Like 'abc123mm'

How does Tsql determine if a char string is numeric or not?


Ric replied on 22-Jun-07 05:58 PM
Well, I just discovered the IsNumeric function.  Now I know how to do this.
Erland Sommarskog replied on 24-Jun-07 08:01 PM
Rich (Rich@) writes:

It depends on exactly what you mean with numeric. There is a function
isnumeric(), but it's useless, because it returns 1, if the value
can be converted to any numeric data type, but you don't know which.

In practice, most of the time what you are really looking for is "is
string digits only"? In that case, this will do:

SELECT * FROM tbl WHERE col LIKE '%[^0-9]%"

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
Books Online for SQL Server 2000 at
Ric replied on 24-Jun-07 02:27 AM
Thanks.  Yes.  Your method is better.
cnu replied on 28-May-09 06:12 AM
You are required to be a member to post replies.  After logging in or becoming a member, you will be redirected back to this page.