Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2008
    Posts
    5

    Unanswered: how to convert vb function to sql function

    Hi all!

    I have the following VB function which I am trying to convert to an SQL function but without much success. Could somebody give me a hand please?

    Code:
        Public Function ConvIn2Dec(ByVal strIn As String, ByRef inDetails As fractionDetails) As Decimal 
            'Set return value to 0 
            ConvIn2Dec = 0 
            inDetails.wholeNo = 0 
            inDetails.numerator = 0 
            inDetails.denominator = 0 
            'Check if characters in string is valid 
            If InStr(1, strIn, "/", vbTextCompare) = 0 Then 
                'No fraction is present so number is whole 
                ConvIn2Dec = Val(strIn) 
            Else 
                'Check for divider in wrong places 
                If Left(strIn, 1) = "/" Then Exit Function 
                If Right(strIn, 1) = "/" Then Exit Function 
                'Check characters 
                Dim valid As Boolean = True 
                Dim ind As Integer 
                For ind = 1 To Len(strIn) 
                    If InStr(1, " .0123456789/", Mid(strIn, ind, 1), vbTextCompare) = 0 Then 
                        valid = False 
                        Exit For 
                    End If 
                Next 
                'If invalid characters were found then exit 
                If valid = False Then Exit Function 
                'Check for no. of dividers 
                Dim divCount As Integer = 0 
                For ind = 1 To Len(strIn) 
                    If Mid(strIn, ind, 1) = "/" Then 
                        divCount = divCount + 1 
                    End If 
                Next 
                'More than one divider was found 
                If divCount > 1 Then Exit Function 
                'Separate text from left & right of divider 
                Dim divPos As Integer = InStr(1, strIn, "/", vbTextCompare) 
                Dim strLeft As String = Left(strIn, divPos - 1) 
                Dim strRight As String = Right(strIn, Len(strIn) - divPos) 
                'Analyse left string 
                Dim spcPos As Integer = InStr(1, strLeft, " ", vbTextCompare) 
                Dim leftSpc As Integer 
                Dim rightspc As Integer 
                If spcPos = 0 Then 
                    If Val(strLeft) <= 0 Or Val(strRight) <= 0 Then Exit Function 
                    ConvIn2Dec = Val(strLeft) / Val(strRight) 
                    inDetails.numerator = Val(strLeft) 
                    inDetails.denominator = Val(strRight) 
                Else 
                    leftSpc = Val(Left(strLeft, spcPos - 1)) 
                    rightspc = Val(Right(strLeft, Len(strLeft) - spcPos)) 
                    If leftSpc <= 0 Or rightspc <= 0 Then Exit Function 
                    ConvIn2Dec = leftSpc + (rightspc / Val(strRight)) 
                    inDetails.wholeNo = leftSpc 
                    inDetails.numerator = rightspc 
                    inDetails.denominator = Val(strRight) 
                End If 
            End If 
        End Function
    The above function, as you might have guessed serves me to convert a fractional string e.g. "5 3/16" in to a proper fraction "5.1875". In addition to the result, the function also gives a detailed breakdown of the string "5 3/16" into the structure fractionDetails which contains wholeNo, numerator and denominator, which in the case of the above example would be inDetails.wholeNo = 5, inDetails.numerator = 3, inDetails.denominator = 16.

    I know it might be difficult to convert the above function completely to an SQL function, but could I at least have the result back, if not the fractionDetails structure?

    Thanks!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Completely un-tested air code:
    Code:
    CREATE FUNCTION dbo.ConvIn2Dec (
       str_in varchar(10)
    )
      RETURNS decimal (10, 4)
    AS
      BEGIN
        DECLARE @int decimal(10, 4)
              , @dec decimal (10, 4)
              , @str varchar(10)
        
        SET @int = Convert(int, Left(@str_in, CharIndex(' ', @str_in)))
        SET @str = Right(@str_in, Len(@str_in) - CharIndex(' ', @str_in) + 1)
    
        EXEC sp_executesql 'SET @dec = Round(' + @str + ', 4)', N'@dec decimal(10, 4)', @dec = @dec
    
        SET @ret = @int + @dec
    
        RETURN @ret
    
      END
    GO
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    George
    Home | Blog

  4. #4
    Join Date
    Jun 2008
    Posts
    5

    how to convert vb function to sql function

    thanks a lot for your posts guys. I'm sure I'll be able to add the missing bits easily! Thanks again!

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ha, looks like my air code doesn't work anyway - sp_executesql isn't allowed within a function...
    George
    Home | Blog

  6. #6
    Join Date
    Jun 2008
    Posts
    5

    how to convert vb function to sql function

    Here's my final (?) function... Do you see any ways to improve it? It takes all sorts of inputs into consideration, even if you enter "a1/2"...

    Code:
    USE [Plyfoam(PR)]
    GO
    /****** Object:  UserDefinedFunction [dbo].[ConvIn2Dec]    Script Date: 12/16/2008 09:37:30 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE FUNCTION [dbo].[ConvIn2Dec](@strIn nvarchar(10))
    RETURNS DECIMAL(10,2)
    BEGIN
    DECLARE @result DECIMAL(10,2)
    --SET @strIn = REPLACE(@strIn,SUBSTRING(@strIn,PATINDEX('%[^.1234567890]%',@strIn),1),'')
    SELECT @result=0
        --Check if characters in @strIng is @valid 
        If CharIndex('/',@strIn, 1) = 0
             Begin 
            --No fraction is present so number is whole 
    		Select @strIn=Replace(@strIn,Substring(@strIn, Patindex('%[^.1234567890]%',@strIn),1),'')
            Select @result = Cast(@strIn AS DECIMAL(10,2))
            End
        Else 
             Begin
            --Check for divider in wrong places 
            If Left(@strIn, 1) = '/'  Return @result
            If Right(@strIn, 1) = '/'  Return @result
            --Check characters 
    
            --If invalid characters were found then exit 
            If @strIn LIKE '%[^ .1234567890]/%'  Return @result
    
            --Check for no. of dividers 
            If @strIn LIKE '%/%/%'  Return @result
    
            --Separate text from left & right of divider 
            Declare @divPos Integer 
            Declare @strLeft NVarchar(32) 
            Declare @strRight NVarchar(32) 
            Declare @spcPos Integer 
            Select @divPos = CharIndex('/', @strIn, 1) 
                            , @strLeft = Left(@strIn, @divPos - 1) 
                            , @strRight = Right(@strIn, Len(@strIn) - @divPos) 
                            , @spcPos = CharIndex(' ', @strLeft, 1) 
    
            --Analyse left @strIng 
            Declare @leftSpc As Integer 
            Declare @rightspc As Integer 
            If @spcPos = 0 
                     Begin
                If CAST(@strLeft as Numeric(18,9)) <= 0 Or Cast(@strRight as Numeric(18,9)) <= 0  Return NULL
                Select @result = Cast(@strLeft as Numeric(18,9)) / Cast(@strRight as Numeric(18,9)) 
                     End
            Else 
                     Begin
                Select @leftSpc = Cast(Left(@strLeft, @spcPos - 1) as Numeric(18,9)) 
                                    , @rightspc = Cast(Right(@strLeft, Len(@strLeft) - @spcPos) as Numeric) 
                If @leftSpc <= 0 Or @rightspc <= 0  Return NULL
                Select @result = @leftSpc + (@rightspc / Cast(@strRight as Numeric(18,9))) 
             End 
         End
            Return @result
    
    END

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2007
    Posts
    183

  9. #9
    Join Date
    Jan 2015
    Posts
    1

    Post I need help in converting vb script to sql stored procedure.

    Hi all,

    Could somebody help me in converting the below vb script to sql stored procedure. This scripting is about the conditions applied for extracting a user defined text from barcode .

    If IsNumeric(Mid(Trim(Me.txtCode.Text), 1, 1)) Then
    Dim FirstChar As String
    Dim startpos As Integer
    Dim totpos As Integer
    startpos = 2
    FirstChar = Mid(Trim(Me.txtCode.Text), 1, 2)
    If FirstChar = "23" Then
    FirstChar = "M"
    startpos = 3
    totpos = 7
    ElseIf FirstChar = "24" Then
    FirstChar = "N"
    startpos = 3
    totpos = 7
    ElseIf Mid(Trim(Me.txtCode.Text), 1, 1) <> "9" Then
    FirstChar = ""
    startpos = 1
    totpos = 8
    Else
    If Len(Trim(Me.txtCode.Text)) > 14 And Mid(Trim(Me.txtCode.Text), 1, 1) = "9" Then
    FirstChar = ""
    startpos = 1
    totpos = 8
    Else
    FirstChar = ""
    totpos = 8
    End If
    End If

    Me.txtCode.Text = FirstChar & Trim(Mid(Trim(Me.txtCode.Text), startpos, totpos))
    End If

  10. #10
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    [quote] I know it might be difficult to convert the above function completely to an SQL function, but could I at least have the result back, if not the fractionDetails structure? [\quote]

    Your example of "5/16" makes me think this might be a conversion for machine US units in fractional inches and not a general rational number tool. If this is true, then create a look-up table. SQL is an awful computational language, but really good for data.

    this is how I do statistical distributions, IRR, etc.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •