How To Convert An Ebcdic Zoned Decimal To A Decimal In Ms Sql Server 2014?

Hi everyone this is major chunk for me I have been trying lot but not getting success there is data in db as [AH_TRAN_CV] of length 9 as Integer. and I want that in varchar with 9

Solution 1:

This encoding isn't a numeric format that can be parsed to a number. It's an EBCDIC zoned decimal encoding found only in mainframes.

You can create a function that splits the string and generates the appropriate sign and last digit from the input string, eg :

createfunction ZonedToNumber(@somenumvarchar(20))
returnsvarchar(20)
asbegindeclare@cchar=right(@somenum,1);

declare@digitchar=casewhen@cbetween'A'and'I'then (ascii(@c) -64)
                        when@cbetween'J'and'R'then (ascii(@c) -73)
                        when@cin ('{','}') then0elsenullenddeclare@signchar=casewhen@cbetween'A'and'I'or@c='{'then'+'when@cbetween'J'and'R'or@c='}'then'-'elsenullendRETURN CONCAT(@sign,left(@somenum,len(@somenum)-1)  ,@digit)
end

Instead of performing any clever arithmetic, the function uses CASE and subtracts a base ASCII value for each case to generate the last digit and sign.

You can apply that function to a zoned decimal string to convert it to an actual number, eg: SELECT dbo.ZonedToNumber('1234') will return =1234. You can also use it in a query :

declare@ttable (num varchar(20))
insertinto@tvalues
('123D'),
('123K'),
('123{'),
('123}')

selectcast(dbo.ZonedToNumber(num) asint)
from@t x

This will return :

1234
-1232
1230
-1230

You should use this function only to convert zoned decimals to actual numbers when loading the data. Almost nobody (outside mainframes) uses this format anymore.