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

LVL 1
mlcktmguyAsked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
OK,

As a function
create function dbo.udf_convert_cobol_number (@cobolnum varchar(50), @scale decimal(5,2) = 0)
returns decimal(14,2)
as
BEGIN

   declare @num decimal(14,2)

   set @cobolnum = 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

   If @scale in (0,1)
      set @num = iif(isnumeric(@cobolnum) = 1,iif(try_parse(@cobolnum as int) = NULL, 0, cast(@cobolnum as int)),0) 
   else
      set @num = iif(isnumeric(@cobolnum) = 1,iif(try_parse(@cobolnum as decimal(14,2)) = NULL, 0.00, cast(@cobolnum as decimal(14,2))),0.00) * @scale

   return (@num)

END
GO

Open in new window


Now, using it with the sample set of numbers.... Including adding in '41672]' and if last char is a J means -416721 ... not -0, or if a closing square bracket, then not a number...
select dbo.udf_convert_cobol_number(cobolnum,0.01) as newnum
from (select '41D' as cobolnum union all
      select '327{' union all
      select '327}' union all
      select '6789Q' union all
      select '1234567M' union all
      select '1234567' union all
      select '41672]' union all
      select '41672J') numbers 

-- results
/*
                                 newnum
---------------------------------------
                                   4.14
                                  32.70
                                 -32.70
                                -678.98
                             -123456.74
                               12345.67
                                   0.00
                               -4167.21

(8 rows affected)
*/

Open in new window

 Notice the @scale so we can apply a scaling factor - because cobol uses signed overpunch and normally implied decimals. so, we nominate a scale 0f 0.01 to get it back into decimal form. A scale of 0 is basically integer (even though it returns .00) and easy to then load as an integer, or cast/convert etc.

The big difference is in error handling. Now, I have assumed return 0 if not a number. Try the number '41672]' or 'E4162J' in either solutions, and you will see a difference.

You have to incorporate error handling of some description. Cannot return a value of 41672 when it (originally 41672] )  is not a legit number, and cannot error if given a random value (e.g. *). Unfortunately data doesnt always come from another system 'clean and ready to convert' assuming it will immediately obey all the rules.

Admittedly, I dont need both isnumeric() and try_parse() functions and can probably get rid of the inner iif(try_parse()) function.
0
 
Máté FarkasDatabase Developer and AdministratorCommented:
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.
0
 
mlcktmguyAuthor Commented:
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...
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Mark WillsTopic AdvisorCommented:
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

0
 
Máté FarkasDatabase Developer and AdministratorCommented:
Then much simpler:
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" & passedStr
Numbers = "0123456789{ABCDEFGHIJKLMNOPQR}"
C = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 1, 2, 3, 4, 5, 6, 7, 8, 9, 0)

Index = InStr(1, Numbers, Mid(Value, Len(Value), 1), vbTextCompare)
Value = Left(Value, Len(Value) - 1) & C(Index)
If Index > 20 Then Value = "-" & Value
convertImportLongStrToNumber = CLng(Value)
End Function

Open in new window


and then here is the function in SQL:
create function dbo.convertImportLongStrToNumber(@passedStr varchar(10))
returns bigint
as
begin
declare @value varchar(20);
declare @index int;
declare @numbers varchar(30) = '0123456789{ABCDEFGHIJKLMNOPQR}';
declare @chars varchar(30)   = '012345678901234567891234567890';

set @value = '0' + @passedStr;
set @index = charindex(substring(@value, len(@value), 1), '0123456789{ABCDEFGHIJKLMNOPQR}')
set @value = left(@value, len(@value) - 1) + substring(@chars, @index, 1)
if @index > 20 set @value = '-' + @value;
return cast(@value as bigint);
end

Open in new window

0
 
Scott PletcherSenior DBACommented:
Performance is key, especially on a scalar function.  So don't use local function variables (unless absolutely required, which is not here) and do as few comparisons/calcs/concats/etc. as reasonably possible:


CREATE FUNCTION dbo.convertImportLongStrToNumber
(
    @passedStr varchar(30)
)
RETURNS bigint
AS
BEGIN
RETURN (
    SELECT CAST(
        sign +  LEFT(@passedStr, LEN(@passedStr) - 1) +
        CASE WHEN RIGHT(@passedStr, 1) BETWEEN '0' AND '9' THEN RIGHT(@passedStr, 1)
                  WHEN RIGHT(@passedStr, 1) IN ('{', '}') THEN '0'
                  ELSE CAST((ASCII(RIGHT(@passedStr, 1)) - 64 + CASE WHEN sign = '-' THEN 1 ELSE 0 END) % 10 AS char(1)) END
        AS bigint) AS returnValue
    FROM (
        SELECT CASE WHEN RIGHT(@passedStr, 1) BETWEEN 'J' AND 'R' OR RIGHT(@passedStr, 1) = '}'
            THEN '-' ELSE '+' END AS sign
    ) AS calc1
)
END /*FUNCTION*/
0
 
Mark WillsTopic AdvisorCommented:
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.......
0
 
mlcktmguyAuthor Commented:
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.
0
 
Scott PletcherSenior DBACommented:
>> 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('\')
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.