how to change a function to return a numeric value

How can I convert this function to return a number
create function dbo.replaceLastWithDigit(@s varchar(max))
returns varchar(max) as begin
set @s = stuff(@s,len(@s),1,'') +
case right(@s,1)
when 'A' then '1'
when 'B' then '2'
...
when 'I' then '9'
when 'J' then '-1'
when 'K' then '-2'
...
when 'ü' then '0'
end
return @s
end
GO
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
assuming int is big enough for the value returned, AND the resulting replacement ends up with a valid number (reporesented as string), this is the function:

create function dbo.replaceLastWithDigit(@s varchar(max))
returns int as begin
set @s = stuff(@s,len(@s),1,'') +
case right(@s,1)
when 'A' then '1'
when 'B' then '2'
...
when 'I' then '9'
when 'J' then '-1'
when 'K' then '-2'
...
when 'ü' then '0'
end
return Cast(@s As int)
end
GO

you only change the returns ... data type to int, and CAST the returned string as an integer
Commented:
You know the dots are placeholders right? That they should be replaced with more "when.. " statements?
Ok, with that in mind, this will work

create function dbo.replaceLastWithDigit(@s varchar(max))
returns int as begin
set @s = stuff(@s,len(@s),1,
case right(@s,1)
when 'A' then '1'
when 'B' then '2'
--  ...
when 'I' then '9'
when 'J' then '-1'
when 'K' then '-2'
--  ...
when 'ü' then '0'
end)
return case when @s like '%[^0-9]%' or len(@s)=0 then -1 else @s end
end
GO

However, there is a clash with the likes of J, K.
If you pass in '1234K', it becomes '1234-2'  (k= -2), which is not a valid int.
Think about what you are really after.
Commented:
I suspect this is what you are are after:
``````ALTER FUNCTION [dbo].[udf_ConvertZonedDecimal](
@ZonedDecimal varchar(20)
)

RETURNS bigint

AS

BEGIN

DECLARE @Sign char(1)

SELECT	@Sign = RIGHT(@ZonedDecimal, 1)

Return 	(
CAST(LEFT(@ZonedDecimal, LEN(@ZonedDecimal) - 1) AS bigint) * 10 +
CASE
WHEN CHARINDEX(@Sign, '{ABCDEFGHI') > 0  THEN CHARINDEX(@Sign, '{ABCDEFGHI') - 1
WHEN CHARINDEX(@Sign, '}JKLMNOPQR') > 0 THEN CHARINDEX(@Sign, '}JKLMNOPQR') - 1
END
)
END
``````
Commented:
Let's try that again:
``````CREATE FUNCTION [dbo].[udf_ConvertZonedDecimal](
@ZonedDecimal varchar(20)
)

RETURNS bigint

AS

BEGIN

DECLARE @Sign char(1)

SELECT	@Sign = RIGHT(@ZonedDecimal, 1)

Return 	(

CASE
WHEN CHARINDEX(@Sign, '{ABCDEFGHI') > 0 THEN CAST(LEFT(@ZonedDecimal, LEN(@ZonedDecimal) - 1) AS bigint) * 10 + CHARINDEX(@Sign, '{ABCDEFGHI') - 1
WHEN CHARINDEX(@Sign, '}JKLMNOPQR') > 0 THEN -(CAST(LEFT(@ZonedDecimal, LEN(@ZonedDecimal) - 1) AS bigint) * 10 + CHARINDEX(@Sign, '}JKLMNOPQR') - 1)
END
)
END
``````

Experts Exchange Solution brought to you by