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

create function dbo.replaceLastWithDigit(@

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

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

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.

Ok, with that in mind, this will work

create function dbo.replaceLastWithDigit(@

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.

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

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

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Microsoft SQL Server

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

create function dbo.replaceLastWithDigit(@

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