Menu
is there an easy way to figure out if a varchar is a number?
Examples:
abc123 --> no number
123 --> yes, its a number
Thanks :)
381
SQL Server ISNUMERIC() Function The ISNUMERIC() function tests whether an expression is numeric. This function returns 1 if the expression is numeric, otherwise it returns 0.
Check if a string ISNUMERIC()? There is an inbuilt function ISNUMERIC() in SQL Server which checks and returns TRUE if the string has “only digits” (so it is a number) otherwise FALSE.
As the name suggests, varchar means character data that is varying. Also known as Variable Character, it is an indeterminate length string data type. It can hold numbers, letters and special characters.
Method 1 - Using CHARINDEX() function This function is used to search for a specific word or a substring in an overall string and returns its starting position of match. In case no word is found, then it will return 0 (zero).
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]%' ISNUMERIC will do
Check the NOTES section too in the article.
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
