Parsing INTERNAL REPRESENTATION OF OVERPUNCH SIGNS

jamesphenry, 2018-11-07 (first published: 2018-10-31)

"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

Rate

Share

Share

Rate

Related content