asked on

SQL Server Function Programming

I am relatively new t SQL Server and very new to SQL Server functions, I am trying to convert some MS Access function to SQL Server Functions.

This particular function needs a looping mechanism, the ability to identify a single character is a passed string and the ability to concatenate two strings.

Can anyone show me how these things are done in the SQL Server Function language.

This function converts an input string generated as an export from a cobol program into a long number. The last byte of the incoming string is the sign (+ or -) byte that also contains a numeric value that must be retained.

Here's the Access function:

Public Function convertImportLongStrToNumber(passedStr As String) As Long
'
Dim wkChar As String
Dim returnChar As String
Dim wkReturnNum As Long
'
Dim wkOutStr As String
Dim wkStrLength As Integer
Dim isItPositive As Boolean
Dim isItNegative As Boolean
'
' get the length of the passed string
wkStrLength = Len(passedStr)
If wkStrLength = 0 Then
    convertImportLongStrToNumber = 0
    Exit Function
End If
'
Dim i As Integer
'
wkOutStr = ""
'
' Loop thru all the characters in the passed string
'
For i = 1 To wkStrLength

    wkChar = Mid(passedStr, i, 1)
    '
'   ' the last character will contain the sign of the number.  If not the last character just concatenate
    '
    If i = wkStrLength Then ' must check the last digit for positive or negative
        '
        ' stuff to do with the last charachter
       '
        checkLastDigit wkChar, _
                       isItPositive, _
                       isItNegative, _
                       returnChar
        '
        concatenate the last character
        wkOutStr = wkOutStr & returnChar
    Else
       ' concatenate characters prior ot the last character
        wkOutStr = wkOutStr & wkChar
    End If
    '
Next i
'
wkReturnNum = Val(wkOutStr)
'
If isItNegative Then
    wkReturnNum = wkReturnNum * -1
End If
'
convertImportLongStrToNumber = wkReturnNum
'
End Function

Open in new window

This is the function called by the above function.

Public Function checkLastDigit(passedChar As String, _
                               passedIsItPositive As Boolean, _
                               passedIsItNegative As Boolean, _
                               returnChar As String)

passedIsItPositive = False
passedIsItNegative = False
'
returnChar = passedChar
'
Select Case passedChar
'
'  +0         {              C0
'  +1         A              C1
'  +2         B              C2
'  +3         C              C3
'  +4         D              C4
'  +5         E              C5
'  +6         F              C6
'  +7         G              C7
'  +8         H              C8
'  +9         I              C9
    '
    Case "{"
        returnChar = "0"
        passedIsItPositive = True
    Case "A"
        returnChar = "1"
        passedIsItPositive = True
    Case "B"
        returnChar = "2"
        passedIsItPositive = True
    Case "C"
        returnChar = "3"
        passedIsItPositive = True
    Case "D"
        returnChar = "4"
        passedIsItPositive = True
    Case "E"
        returnChar = "5"
        passedIsItPositive = True
    Case "F"
        returnChar = "6"
        passedIsItPositive = True
    Case "G"
        returnChar = "7"
        passedIsItPositive = True
    Case "H"
        returnChar = "8"
        passedIsItPositive = True
    Case "I"
        returnChar = "9"
        passedIsItPositive = True
'
'  -0         }              D0
'  -1         J              D1
'  -2         K              D2
'  -3         L              D3
'  -4         M              D4
'  -5         N              D5
'  -6         O              D6
'  -7         P              D7
'  -8         Q              D8
'  -9         R              D9
    Case "}"
        returnChar = "0"
        passedIsItNegative = True
    Case "J"
        returnChar = "1"
        passedIsItNegative = True
    Case "K"
        returnChar = "2"
        passedIsItNegative = True
    Case "L"
        returnChar = "3"
        passedIsItNegative = True
    Case "M"
        returnChar = "4"
        passedIsItNegative = True
    Case "N"
        returnChar = "5"
        passedIsItNegative = True
    Case "O"
        returnChar = "6"
        passedIsItNegative = True
    Case cPropertyLookupStr
        returnChar = "7"
        passedIsItNegative = True
    Case "Q"
        returnChar = "8"
        passedIsItNegative = True
    Case "R"
        returnChar = "9"
        passedIsItNegative = True
End Select
'
End Function

Open in new window

Máté Farkas

Hi, I think your function solves that problem on a too complex way. If you simplify it then you can easily covert it to SQL language:

Option Base 1
Option Explicit

Public Function convertImportLongStrToNumber(passedStr As String) As Long
Dim I As Integer
Dim C
Dim Numbers As String
Dim Index As Integer
Dim Value As String

Value = "0"
Numbers = "{ABCDEFGHIJKLMNOPQR}"
C = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 1, 2, 3, 4, 5, 6, 7, 8, 9, 0)

For I = 1 To Len(passedStr)
    Index = InStr(1, Numbers, Mid(passedStr, I, 1), vbTextCompare)
    Value = Value & C(Index)
Next I

If Index > 10 Then Value = "-" & Value
convertImportLongStrToNumber = CLng(Value)
End Function

Open in new window

BTW, can you send us some examples what is the input and output of this function? Then we can help you to covert it to sql.

mlcktmguy

ASKER

Thanks but your function doesn't work. The input to this function will be a string of number except possibly the last character which may be non numeric depending on the sign.

If the last character is 'A', the last digit is 1 and the sign is +, if the last character is a '}'. the last digit is 0 and the sign is -.

Input to this function could be a numeric string (except possibly the last character) of any length, up to length of 9.
The last character does not have to have a sign but it must be checked for. The lack of a sign in the last character means the number will be positive

Input Result
41D +414
327{ +3270
327} -3270
6789Q -67898
1234567M -12345674
1234567 +1234567
41672] -416720

etc...

Mark Wills

If you were to pass in @cobolnum as a parameter, then something like

select case when right(@cobolnum,1) in ('J','K','L','M','N','O','P','Q','R') then '-'+left(@cobolnum,len(@cobolnum)-1)+char(ascii(right(@cobolnum,1))-25)
            when right(@cobolnum,1) in ('A','B','C','D','E','F','G','H','I') then left(@cobolnum,len(@cobolnum)-1)+char(ascii(right(@cobolnum,1))-16)
            when right(@cobolnum,1) = '}' then '-'+left(@cobolnum,len(@cobolnum)-1)+'0'
            when right(@cobolnum,1) = '{' then left(@cobolnum,len(@cobolnum)-1)+'0'
            when isnumeric(@cobolnum) = 1 then @cobolnum
		end

Open in new window

SOLUTION

Máté Farkas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Wills

Alright, if I go back to my original post and simply wrap that in a try_convert()

But keep the @scale for the implied decimal convert then the minimum I think we can get away with is

alter function dbo.udf_convert_cobol_number (@cobolnum varchar(50), @scale decimal(5,2))
returns decimal(14,2)
as
BEGIN
   return try_cast((case when right(@cobolnum,1) in ('J','K','L','M','N','O','P','Q','R') then '-'+left(@cobolnum,len(@cobolnum)-1)+char(ascii(right(@cobolnum,1))-25)
                         when right(@cobolnum,1) in ('A','B','C','D','E','F','G','H','I') then left(@cobolnum,len(@cobolnum)-1)+char(ascii(right(@cobolnum,1))-16)
                         when right(@cobolnum,1) = '}' then '-'+left(@cobolnum,len(@cobolnum)-1)+'0'
                         when right(@cobolnum,1) = '{' then left(@cobolnum,len(@cobolnum)-1)+'0'
                         else @cobolnum
                    end) as decimal(14,2)) * @scale

END
GO

Open in new window

But we end up with NULL instead of ZERO if there is an error, and testing on over 4 million rows is the same performance as others in this thread - especially if we compare apples and multiply by 0.01 to get the decimal aspect.... Still we keep an element of error checking.

Being able to rely on results is extremely important if dealing with volumes, otherwise, you need to write additional routines to test the results. So, always keep your eye on the big picture.

And we can get rid of the @scale and return integer if that's what is needed.......

mlcktmguy

ASKER

Three, four since Mark submitted 2, very good solutions. I chose Mark's fist solution because it will return 0 in the event of an invalid string being passed. I have encountered many, many anomoly's /garbage in the clients data that I am converting so this is very important.

Thanks to all for your interest and help.

Scott Pletcher

>> because it will return 0 in the event of an invalid string being passed. <<

Not necessarily:

--all return 1 = "numeric" (my favorite is the \)
SELECT ISNUMERIC('10E0'),ISNUMERIC('23,444'),ISNUMERIC('4D1'),ISNUMERIC('$')
SELECT ISNUMERIC('.'), ISNUMERIC(','),ISNUMERIC('\')