DataAnalyst011 (6/27/2013)
From time to time I need to check if a column is completely numeric (or usually, check for the row contain something other than numeric). I've read the ISNUMERIC has problems. I've used LIKE '%[0-9]%'? successfully, but even after reading around in several places I still don't understand how it works....here's an example of another version I've seen: NOT LIKE '%[^0-9]%'
Hi there. First things first: please define "numeric" and what you are REALLY checking for. Are you looking for:
all digitsis a valid number anywhere in the worldis convertible to one of the available number datatypes in SQL ServerThese are all different concepts.
12345 is all digits, a valid number, and convertible to most SQL Server number types.12345.00 is NOT all digits, but is still a valid number, and convertible to DECIMAL / FLOAT / REAL.12,345 is NOT all digits, but is still a valid number, and convertible to MONEY.123.45E+03 is NOT all digits, but is still a valid number, and convertible to FLOAT / REAL.12.345.678,9 is NOT all digits, is NOT convertible, but is still a valid number in some locales12 345 678,9 is NOT all digits, is NOT convertible, but is still a valid number in some locales (such as fr-FR)23847234872893475983479583749583749573945739 is all digits, is a valid number, but is NOT convertible to any SQL Server number types as it is larger than 38 digitsSo first you need to be clear on what you will accept as being a number and what is not valid. Then you have several options, namely:
LIKE operator as you have used before: LIKE '%[^0-9]%'. This will find rows that are NOT all digits, but won't catch NULL or empty, which may or may not be acceptable so you might need additional WHERE clauses.If you are running SQL Server 2012 (or newer at some point) use the new TRY_PARSE() functionString_IsNumeric function (free in SQL# (SQLsharp)[/url])RegEx functions (free in SQL# (SQLsharp)[/url])Take care,
Solomon...