• 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 digits
  • is a valid number anywhere in the world
  • is convertible to one of the available number datatypes in SQL Server
  • These 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 locales
  • 12 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 digits
  • So 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() function
  • String_IsNumeric function (free in SQL# (SQLsharp)[/url])
  • RegEx functions (free in SQL# (SQLsharp)[/url])
  • Take care,

    Solomon...

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR