How to determine the field value which can not convert to (decimal, float,int) in SQL Server

by

Plain ISNUMERIC is rubbish

  • Empty string, +, - and . are all valid
  • So is +. etc
  • 1e-3 is valid for float but not decimal (unless you CAST to float then to decimal)

For a particularly cryptic but failsafe solution, append e0 or .0e0 then use ISNUMERIC

SELECT
   ISNUMERIC(MyCOl + 'e0')   --decimal check,
   ISNUMERIC(MyCOl + '.0e0')  --integer check

So

SELECT
    ID, VALUE,
    CAST(
          CASE WHEN ISNUMERIC(VALUE + 'e0') = 1 THEN VALUE ELSE NULL END
          AS decimal(38, 10)
        ) AS ConvertedVALUE
FROM
    Mytable

More Related Contents:

  1. Date conversion and culture: Difference between DATE and DATETIME
  2. T-sql – determine if value is integer
  3. SQL Server – error when creating stored procedure
  4. How can foreign key constraints be temporarily disabled using T-SQL?
  5. Insert multiple rows WITHOUT repeating the “INSERT INTO …” part of the statement?
  6. How can I escape square brackets in a LIKE clause?
  7. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  8. Parameter Sniffing (or Spoofing) in SQL Server
  9. SQL Server String or binary data would be truncated
  10. Datatype for storing ip address in SQL Server
  11. How to change identity column values programmatically?
  12. SQL Server Regular expressions in T-SQL
  13. Maximum size of a varchar(max) variable
  14. best way to convert and validate a date string
  15. Concatenating Column Values into a Comma-Separated List
  16. Is there a way to simplify a NULL compare of 2 values
  17. TSQL Try / Catch within Transaction or vice versa?
  18. What is the maximum number of characters that nvarchar(MAX) will hold?
  19. SQL server schema and default schema
  20. SQL to find first non-numeric character in a string
  21. Get ROWS as COLUMNS (SQL Server dynamic PIVOT query)
  22. SQL Server 2008 – How do i return a User-Defined Table Type from a Table-Valued Function?
  23. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  24. Cannot bulk load. Operating system error code 5 (Access is denied.)
  25. comparing a column to a list of values in t-sql
  26. Does query plan optimizer works well with joined/filtered table-valued functions?
  27. Random record from a database table (T-SQL)
  28. What are row, page and table locks? And when they are acquired?
  29. Random Weighted Choice in T-SQL
  30. time format in SQL Server

Leave a Comment