Parsing INTERNAL REPRESENTATION OF OVERPUNCH SIGNS
"The Invoice Report and Confirmation of Payment Report contain monetary fields
formatted with over-punch characters, or Extended Binary Coded Decimal
Interchange Code (EBCDIC)."
"00000000035A (In the example to the left the A converts to the number one (1) making the number 351.
Because this is a monetary field and the decimal is implied the true monetary value is $3.51.
The number is considered positive because the letter A falls under the “Signed Positive” column (see chart below). "
-- INTERNAL REPRESENTATION OF OVERPUNCH SIGNS
-- POSITIVE A B C D E F G H I {
-- Numeric value 1 2 3 4 5 6 7 8 9 0
-- NEGATIVE J K L M N O P Q R }
SELECT CAST([dbo].[udf_OverPunchConversion]('00000000035A') AS MONEY)
Result: 3.51
SELECT CAST([dbo].[udf_OverPunchConversion]('00000000000{') AS MONEY)
Result: 0.00
Enjoy!
-- ================================================ --
-- Author: Bernabe Diaz -- --
-- Description: --
-- INTERNAL REPRESENTATION OF OVERPUNCH SIGNS --
-- POSITIVE A B C D E F G H I { --
-- Numeric value 1 2 3 4 5 6 7 8 9 0 --
-- NEGATIVE J K L M N O P Q R } --
-- ================================================ --
CREATE FUNCTION [dbo].[udf_OverPunchConversion]
(
-- Add the parameters for the function here
@IROS VARCHAR(20)
)
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @ResultVar VARCHAR(20),@Overpunch VARCHAR(1),@pos INT
SET @pos=LEN(LTRIM(RTRIM(@IROS)))-1
SET @Overpunch=RIGHT(LTRIM(RTRIM(@IROS)),1)
SELECT @ResultVar=CASE @Overpunch
WHEN 'A' THEN STUFF(REPLACE(@IROS,@Overpunch,'1') ,@pos,0,'.')
WHEN 'B' THEN STUFF(REPLACE(@IROS,@Overpunch,'2') ,@pos,0,'.')
WHEN 'C' THEN STUFF(REPLACE(@IROS,@Overpunch,'3') ,@pos,0,'.')
WHEN 'D' THEN STUFF(REPLACE(@IROS,@Overpunch,'4') ,@pos,0,'.')
WHEN 'E' THEN STUFF(REPLACE(@IROS,@Overpunch,'5') ,@pos,0,'.')
WHEN 'F' THEN STUFF(REPLACE(@IROS,@Overpunch,'6') ,@pos,0,'.')
WHEN 'G' THEN STUFF(REPLACE(@IROS,@Overpunch,'7') ,@pos,0,'.')
WHEN 'H' THEN STUFF(REPLACE(@IROS,@Overpunch,'8') ,@pos,0,'.')
WHEN 'I' THEN STUFF(REPLACE(@IROS,@Overpunch,'9') ,@pos,0,'.')
WHEN '{' THEN STUFF(REPLACE(@IROS,@Overpunch,'0') ,@pos,0,'.')
WHEN 'J' THEN '-'+STUFF(REPLACE(@IROS,@Overpunch,'1') ,@pos,0,'.')
WHEN 'K' THEN '-'+STUFF(REPLACE(@IROS,@Overpunch,'2') ,@pos,0,'.')
WHEN 'L' THEN '-'+STUFF(REPLACE(@IROS,@Overpunch,'3') ,@pos,0,'.')
WHEN 'M' THEN '-'+STUFF(REPLACE(@IROS,@Overpunch,'4') ,@pos,0,'.')
WHEN 'N' THEN '-'+STUFF(REPLACE(@IROS,@Overpunch,'5') ,@pos,0,'.')
WHEN 'O' THEN '-'+STUFF(REPLACE(@IROS,@Overpunch,'6') ,@pos,0,'.')
WHEN 'P' THEN '-'+STUFF(REPLACE(@IROS,@Overpunch,'7') ,@pos,0,'.')
WHEN 'Q' THEN '-'+STUFF(REPLACE(@IROS,@Overpunch,'8') ,@pos,0,'.')
WHEN 'R' THEN '-'+STUFF(REPLACE(@IROS,@Overpunch,'9') ,@pos,0,'.')
WHEN '}' THEN '-'+STUFF(REPLACE(@IROS,@Overpunch,'0') ,@pos,0,'.')
END
-- Return the result of the function
RETURN @ResultVar
END 
