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 StringDim returnChar As StringDim wkReturnNum As Long'Dim wkOutStr As StringDim wkStrLength As IntegerDim isItPositive As BooleanDim isItNegative As Boolean'' get the length of the passed stringwkStrLength = Len(passedStr)If wkStrLength = 0 Then convertImportLongStrToNumber = 0 Exit FunctionEnd 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 * -1End If'convertImportLongStrToNumber = wkReturnNum'End Function

create function dbo.udf_convert_cobol_number (@cobolnum varchar(50), @scale decimal(5,2) = 0)returns decimal(14,2)asBEGIN 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)ENDGO

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 newnumfrom (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)*/

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.

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 1Option ExplicitPublic Function convertImportLongStrToNumber(passedStr As String) As LongDim I As IntegerDim CDim Numbers As StringDim Index As IntegerDim Value As StringValue = "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 IIf Index > 10 Then Value = "-" & ValueconvertImportLongStrToNumber = CLng(Value)End Function

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

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.

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

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*/

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)asBEGIN 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)) * @scaleENDGO

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.......

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.

As a function

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...

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.