Check if a varchar is a number (TSQL)

grady picture grady · Jan 5, 2011 · Viewed 130.5k times · Source

is there an easy way to figure out if a varchar is a number?

Examples:

abc123 --> no number

123 --> yes, its a number

Thanks :)

Answer

Damien_The_Unbeliever picture Damien_The_Unbeliever · Jan 5, 2011

ISNUMERIC will not do - it tells you that the string can be converted to any of the numeric types, which is almost always a pointless piece of information to know. For example, all of the following are numeric, according to ISNUMERIC:

£, $, 0d0

If you want to check for digits and only digits, a negative LIKE expression is what you want:

not Value like '%[^0-9]%'