IsNumeric(data)  is a widely used function in SQL Server programming.  If passed in data is a number – it returns 1, otherwise 0. Supposedly. There’re several cases when it returns 1 even though the data is not numeric. Some of those cases are: dollar sign “$”, minus sign “-“, space, tab. If you try to call Cast or Convert function on the data after that to convert data to number – they will throw an error

An excellent article at SQL Hacks suggest adding “e0” to the data when passing it to IsNumeric function. What it does essentualy is making the number to be in scientific format, e.g 12.34 becomes 12.34e0 (or 12.34 * 1). Which still remains a number. So IsNumeric(12.34e0) returns 1 while IsNumeric($e0) returns 0.

Which works great until actual number in scientific format is passed.  If you try to test for example 1.234e5, IsNumeric correctly returns 1, but by adding “e0” as article suggest IsNumeric(1.2345e5e0) returns 0.

To work around this limitation I simple test for an “e” within passed data. If it exist, I am calling IsNumeric as usual, if it does not – I am adding “e0” to the tested data:

CREATE FUNCTION IsRealNumber (@i_sValueToTest nvarchar(250))
/* This function tests if passed in value a real number */

RETURNS int
AS
BEGIN
       DECLARE @iResult int

      — if there is ‘E’ in the value – do a standard IsNumeric test
     IF CHARINDEX(‘E’, UPPER(@i_sValueToTest))<> 0
            SET @iResult = IsNumeric(@i_sValueToTest)
      — Otherwise do a modified IsNumeric test by adding ‘E0’ at the end
    ELSE
          SET @iResult = IsNumeric(@i_sValueToTest + ‘E0’)

        RETURN @iResult
END