Validating string for numeric values in SQL Server

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.

Popularity: unranked [?]