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

The Visual Basic CLR function alternate to T-SQL ISNUMERIC

<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

About the Author

Joe Kunk is a Microsoft MVP in Visual Basic, three-time president of the Greater Lansing User Group for .NET, and developer for Dart Container Corporation of Mason, Michigan. He's been developing software for over 30 years and has worked in the education, government, financial and manufacturing industries. Kunk's co-authored the book "Professional DevExpress ASP.NET Controls" (Wrox Programmer to Programmer, 2009). He can be reached via email at joekunk@ajboggs.com.