A CLR Alternative to the SQL Server ISNUMERIC Function: Listing 1

The Visual Basic CLR function alternate to T-SQL ISNUMERIC

• By Joe Kunk
• 10/11/2011

<Microsoft.SqlServer.Server.SqlFunction()>
    Public Shared Function fnIsNumeric(field As SqlString, sqltype As String) As SqlBoolean
        Dim result As New SqlBoolean(0)  'default to False
        Dim errorMessage As String = String.Empty

        'Determine base type and any decimal precision parameters
        Dim lcOption = sqltype.ToString.Trim.ToLower
        Dim lcOptionPrecision = String.Empty
        If (lcOption.Contains("(")) Then
            lcOptionPrecision = lcOption.Substring(lcOption.IndexOf("(") + 1).Replace(")", "").Trim
            lcOption = lcOption.Substring(0, lcOption.IndexOf("("))
        End If

        Try
            Select Case lcOption

                Case "bigint"
                    Dim sqlBigInt = New SqlInt64
                    sqlBigInt = field.ToSqlInt64
                    If (sqlBigInt.IsNull = False) Then result = True

                Case "bit"
                    If (field.Value.Contains("+") OrElse field.Value.Contains("-")) Then
                        result = False
                    Else
                        Dim sqlBit = New SqlByte
                        sqlBit = field.ToSqlByte
                        If (sqlBit.IsNull = False AndAlso (sqlBit = 0 OrElse sqlBit = 1)) Then result = True
                    End If

                Case "decimal", "numeric"
                   'Support format decimal(x,0) or decimal(x,y) where true 
     'only if number fits in precision x,y
                   'Precision = maximum number of digits to the left of the decimal point
                   'If decimal(x,y) supplied, maximum precision = x - y
                    Dim sqlDecimal = New SqlDecimal
                    sqlDecimal = field.ToSqlDecimal
                    If (sqlDecimal.IsNull = False) Then
                        result = True
                        If (lcOptionPrecision.Length > 0) Then
                            Dim parms = lcOption.Split(",".ToCharArray)
                            If (parms.Length > 0) Then
                                Dim precision = 0
                                Integer.TryParse(parms(0), precision)
                                If (precision > 0) Then
                                    If (parms.Length > 1) Then
                                        Dim scale = 0
                                        Integer.TryParse(parms(1), scale)
                                        precision = precision - scale
                                    End If
                                    Dim x = " " + sqlDecimal.Value.ToString.Replace("-", "") + "."
                                    Dim decPrecisionDigitCount = x.Substring(0, x.IndexOf(".")).Trim
                                    If (decPrecisionDigitCount.Length > precision) Then result = False
                                End If
                            End If
                        End If
                    End If

                Case "float"
                    Dim sqlFloat = New SqlDouble()
                    sqlFloat = field.ToSqlDouble
                    If (sqlFloat.IsNull = False) Then result = True

                Case "int"
                    Dim sqlInt = New SqlInt32
                    sqlInt = field.ToSqlInt32
                    If (sqlInt.IsNull = False) Then result = True

                Case "money"
                    Dim sqlMoney = New SqlMoney
                    sqlMoney = field.ToSqlMoney
                    If (sqlMoney.IsNull = False) Then result = True

                Case "real"
                    Dim sqlSingle = New SqlSingle
                    sqlSingle = field.ToSqlSingle
                    If (sqlSingle.IsNull = False) Then result = True

                Case "smallint"
                    Dim sqlSmallInt = New SqlInt16
                    sqlSmallInt = field.ToSqlInt16
                    If (sqlSmallInt.IsNull = False) Then result = True

                Case "smallmoney"
                    Dim sqlSmallMoney = New SqlMoney
                    sqlSmallMoney = field.ToSqlMoney
                    If (sqlSmallMoney.IsNull = False) Then
                        'Ensure that it will fit in a 4-byte small money
                        If (sqlSmallMoney.Value >= -214748.3648 AndAlso
                            sqlSmallMoney.Value <= 214748.3647) Then
                            result = True
                        End If
                    End If

                Case "tinyint"
                    Dim sqlTinyInt = New SqlByte
                    sqlTinyInt = field.ToSqlByte
                    If (sqlTinyInt.IsNull = False) Then result = True

                Case Else
                    errorMessage = "Invalid format option"

            End Select
        Catch ex As Exception
            If (String.IsNullOrEmpty(errorMessage) = False) Then result = SqlBoolean.Null
        End Try
        Return result
    End Function
End Class