Validating string for numeric values in SQL Server
November 5, 2009 by Nauman · Leave a Comment
What to do when you have a large string and you have to validate whether this string only contain digits or not. A straight thing that comes in our mind is to use a looping technique to traverse all the characters in the string and validate each character to be a digit. But how could you implement this in SQL Server?
There is an ISNUMERIC function that checks whether a string can be converted to a valid numeric datatype or not. This function can be helpful for small strings but not for big ones like of length 30 or more. Then what’s the solution?
There is a workaround for this problem, SQL Server provides a function "PATINDEX".
PATINDEX: Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found.
I have used this function like the following code:
Select *
From YourTable
Where PATINDEX(‘%[^0-9]%’, YourColumnName) > 0
If your column contains any character other than numeric digits than it would returns the location of its first occurrence.
