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```

Rate

You rated this post out of 5. Change rating

Rate

You rated this post out of 5. Change rating