Link to home
Start Free TrialLog in
Avatar of kat50
kat50

asked on

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
SOLUTION
Avatar of ErezMor
ErezMor
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial