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

ErezMorCommented:
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
cyberkiwiCommented:
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.
Anthony PerkinsCommented:
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

Open in new window

Anthony PerkinsCommented:
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

Open in new window

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.