Convert function from vba

[Pauldohert]

I'm new to UDF s so to get me started how would this be done in from vba to T-SQL?

Is there somewhere online where I can see some examples of UDF in SQL server?


Thanks

Function CharConvert(strChar As String) As Integer
Select Case strChar
Case "A", "E", "I", "O", "U", "Y"
CharConvert = 0
Case "C", "G", "J", "K", "Q", "S", "X", "Z"
CharConvert = 2
Case "D", "T"
CharConvert = 3
Case "M", "N"
CharConvert = 5
Case "B", "F", "P", "V"
CharConvert = 1
Case "L"
CharConvert = 4
Case "R"
CharConvert = 6
Case Else
CharConvert = -1
End Select
End Function

 

[pdx_man]

CREATE Function dbo.udf_CharConvert(@strChar AS CHAR(1))
RETURNS INT AS
BEGIN
DECLARE @var_ret AS INT

SELECT @var_ret = Case
WHEN @strChar like '[AEIOUY]' THEN 0
WHEN @strChar like ('[CGJKQSXZ]') THEN 2
WHEN @strChar like ('[DT]') THEN 3
WHEN @strChar like ('[MN]') THEN 5
WHEN @strChar like ('[BFPV]') THEN 1
WHEN @strChar = 'L' THEN 4
WHEN @strChar = 'R' THEN 6
ELSE -1
END

RETURN @var_ret
END

-- usage
-- SELECT dbo.udf_CharConvert('V')

As far as where you can find examples of UDFs, try Google

 

[Pauldohert]

Cheers - Thankyou very much.

Thanks for the search, hopefully these examples will put me on the right path for converting a few more functions!

Ta.