The code below does not seem to work when the value is '0000004000' it is returning a value of 4.00 instead of 40.00. Can someone please advise how to convert '0000004000' to 40.00.
Thanks.
Code:
Thanks.
Code:
Code:
DECLARE @i int
, @temp char(1), @tmp varchar(1), @a varchar(1)
, @ebcdic char(2)
, @result VARCHAR(255) , @result1 numeric(22,2)
SET @i=1
SET @result=''
set @a ='0'
WHILE (@i<=LEN(@s))
BEGIN
SET @temp=SUBSTRING(@s,@i,1)
SET @tmp=SUBSTRING(@s,@i,1)
SET @ebcdic=CASE @temp
--POSITIVE
WHEN '{' THEN '0'
WHEN 'A' THEN '1'
WHEN 'B' THEN '2'
WHEN 'C' THEN '3'
WHEN 'D' THEN '4'
WHEN 'E' THEN '5'
WHEN 'F' THEN '6'
WHEN 'G' THEN '7'
WHEN 'H' THEN '8'
WHEN 'I' THEN '9'
--NEGATIVE
WHEN '}' THEN '0'
WHEN 'J' THEN '1'
WHEN 'K' THEN '2'
WHEN 'L' THEN '3'
WHEN 'M' THEN '4'
WHEN 'N' THEN '5'
WHEN 'O' THEN '6'
WHEN 'P' THEN '7'
WHEN 'Q' THEN '8'
WHEN 'R' THEN '9'
ELSE ' '
END
SET @a =CASE @tmp
--NEGATIVE
WHEN '}' THEN '1'
WHEN 'J' THEN '1'
WHEN 'K' THEN '1'
WHEN 'L' THEN '1'
WHEN 'M' THEN '1'
WHEN 'N' THEN '1'
WHEN 'O' THEN '1'
WHEN 'P' THEN '1'
WHEN 'Q' THEN '1'
WHEN 'R' THEN '1'
ELSE '0'
END
IF @ebcdic=''
SET @ebcdic=CASE
WHEN ASCII(@temp) BETWEEN 129 AND 137 THEN CHAR(ASCII(@temp)-32)
WHEN ASCII(@temp) BETWEEN 145 AND 153 THEN CHAR(ASCII(@temp)-39)
WHEN ASCII(@temp) BETWEEN 162 AND 169 THEN CHAR(ASCII(@temp)-47)
WHEN ASCII(@temp) BETWEEN 193 AND 201 THEN CHAR(ASCII(@temp)-128)
WHEN ASCII(@temp) BETWEEN 209 AND 217 THEN CHAR(ASCII(@temp)-135)
WHEN ASCII(@temp) BETWEEN 226 AND 233 THEN CHAR(ASCII(@temp)-143)
WHEN ASCII(@temp) BETWEEN 240 AND 249 THEN CHAR(ASCII(@temp)-192)
ELSE ''
END
SET @result=@result+@ebcdic
SET @i=@i+1
END
set @result1 = convert(numeric,(case when @a ='1' then '-' else '+' end + substring( @s, 1,len(@s)-1) + ltrim(rtrim(@result))))
RETURN @result1/100
END
Comment