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

comments powered by Disqus

Featured

  • ML.NET Improves Object Detection

    Microsoft improved the object detection capabilities of its ML.NET machine learning framework for .NET developers, adding the ability to train custom models with Model Builder in Visual Studio.

  • More Improvements for VS Code's New Python Language Server

    Microsoft announced more improvements for the new Python language server for Visual Studio Code, Pylance, specializing in rich type information.

  • Death of the Dev Machine?

    Here's a takeaway from this week's Ignite 2020 event: An advanced Azure cloud portends the death of the traditional, high-powered dev machine packed with computing, memory and storage components.

  • COVID-19 Is Ignite 2020's Elephant in the Room: 'Frankly, It Sucks'

    As in all things of our new reality, there was no escaping the drastic changes in routine caused by the COVID-19 pandemic during Microsoft's big Ignite 2020 developer/IT pro conference, this week shifted to an online-only event after drawing tens of thousands of in-person attendees in years past.

  • Visual Studio 2019 v16.8 Preview Update Adds Codespaces

    To coincide with the Microsoft Ignite 2020 IT pro/developer event, the Visual Studio dev team shipped a new update, Visual Studio 2019 v16.8 Preview 3.1, with the main attraction being support for cloud-hosted Codespaces, now in a limited beta.

Upcoming Events