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@gmail.com.

comments powered by Disqus

Featured

  • How to Create a Machine Learning Decision Tree Classifier Using C#

    After earlier explaining how to compute disorder and split data in his exploration of machine learning decision tree classifiers, resident data scientist Dr. James McCaffrey of Microsoft Research now shows how to use the splitting and disorder code to create a working decision tree classifier.

  • Microsoft: Move from Traditional ASP.NET to 'Core' Requires 'Heavy Lifting'

    There are plenty of reasons to move traditional ASP.NET web apps -- part of the old .NET Framework -- to the new cross-platform direction, ASP.NET Core, but beware it will require some "heavy lifting," Microsoft says.

  • Purple Blue Nebula Graphic

    How to Compute Disorder for Machine Learning Decision Trees Using C#

    Using a decision tree classifier from a machine learning library is often awkward because it usually must be customized and library decision trees have many complex supporting functions, says resident data scientist Dr. James McCaffrey, so when he needs a decision tree classifier, he always creates one from scratch. Here's how.

  • Blazor's Future: gRPC Is Key

    Blazor guru Steve Sanderson detailed what Microsoft is thinking about the future of the revolutionary project that enables .NET-based web development using C# instead of JavaScript, explaining how gRPC is key, along with a new way of testing and a scheme for installable desktop apps.

  • Don't Do It All Yourself: Exploiting gRPC Well Known Types in .NET Core

    If you're creating business services that send dates and decimal data then you may be concerned that gRPC services don't support the relevant data types. Don't Panic! There are solutions. Here's how to use them.

.NET Insight

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.

Upcoming Events