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

The Visual Basic CLR function alternate to T-SQL ISNUMERIC

    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

            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
                        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 [email protected].

comments powered by Disqus


  • Here's a One-Stop Shop for .NET 5 Improvements

    Culled from reams of Microsoft documentation, here's a high-level summary of what's new for performance, networking, diagnostics and more, along with links to the nitty-gritty details for those wanting to dig in more.

  • Azure SQL Database Ranked Among Top 3 Databases of 2020

    Microsoft touted the inclusion of Azure SQL Database among the top three databases of 2020 in a popularity ranking by DB-Engines, which collects and manages information about database management systems, updating its lists monthly.

  • Time Tracker Says VS Code Is No. 1 Editor for Devs, Some Working 15+ Hours Per Day

    WakaTime, which does time tracking for programmers, released data for 2020 showing that Visual Studio Code is by far the top editor/IDE used by its coders, some of whom are hacking away for more than 15 hours per day.

  • VS Code Java Project Explorer Gets Better

    The recently introduced project view for managing Java projects in Visual Studio Code received several enhancements in the latest update to Java functionality provided in Microsoft's popular open source, cross-platform code editor.

Upcoming Events