PACK() user defined function

,

Convert a decimal number to IBM EBCDIC packed decimal format.  If for some reason you need to export a number to IBM systems in native format, you can define a SQL Server field as binary(8) and Pack() your decimal number into it.  Then IBM mainframes and COBOL/CICS systems can read the numbers from your data base.  See Unpack() script if you need to go the other way.

create FUNCTION dbo.Pack(@Number decimal(15,0))
RETURNS binary(8)
AS
/*****************************************
 *
 * copyright 2002 (c): don frazier
 * all rights reserved.
 *
 * Permission is granted to use this function
 * provided no fee is charge for its use
 * or distribution.
 * 
 * This notice must remain intact in all
 * executable and published copies.
 *
 * Convert an exact number in IBM Packed Decimal
 * format.  This function supports up to
 * 15 significant digits.  To extend it,
 * create another function or change the
 * length of the RETURNS variable to match
 * the precision of the @Number parameter.
 *
 *****************************************/
BEGIN
declare @RC          binary(8)
,       @WorkValue   binary(1)
,       @Units       decimal(16,0)
,       @Tens        decimal(16,0)
,       @StuffLoc    Int
,       @NotUnits    decimal(16,0)

if @Number >= 0
  set @RC = 12  -- packed 0x0C = positive number
else
  set @RC = 13  -- packed 0x0D = negative number

set @Number = abs(@Number)

/***************************************************
 *  all this hokey math essentially gets the digit
 *  in the units position into the @Units variable.
 *  The weird math and +10 step handle all the 
 *  rounding errors that occur.  This same creepy
 *  logic is repeated twice inside the while loop
 *  because every 2 digits are compressed (packed)
 *  into a single byte of output.
 *
 * this bit of logic outside the loop does the sign
 * nibble (half byte) and the least significant
 * digit of the input number.
 ***************************************************/

set @NotUnits = @Number / 10
set @Units = @Number - (@NotUnits * 10)
if @Units < 0 set @Units = @Units + 10

-- least significant byte = units + sign
set @RC = (cast(@Units as int) * 16) + @RC
-- we did the units
set @Number = (@Number - @Units) / 10

set @StuffLoc = 7  -- length of output field - 1

while @Number > 0  -- quit when number is finished
and @StuffLoc > 0  -- or we're out of space in the return value
begin

  -- get next "units" value
  set @NotUnits = @Number / 10
  set @Units = @Number - (@NotUnits * 10)
  if @Units < 0 set @Units = @Units + 10
  set @Number = (@Number - @Units) / 10

  -- get next "Tens" value
  set @NotUnits = @Number  / 10
  set @Tens = @Number - (@NotUnits * 10)
  if @Tens < 0 set @Tens = @Tens + 10
  set @Number = (@Number - @Tens) / 10

  -- stuff it in the appropriate byte
  set @WorkValue = (cast(@Tens as int) * 16) + cast(@Units as int)
  set @RC = cast(stuff(@RC, @StuffLoc, 1, @WorkValue) as binary(8))
  set @StuffLoc = @StuffLoc - 1
end

-- this statement never occurs and can be commented out
-- add it back in during testing if you change the lengths
-- of the input and/or output types.  A NULL value indicates
-- the input value exceeds the capacity of the output value
if @number > 0 set @RC = null

return @rc
end

Rate

5 (1)

Share

Share

Rate

5 (1)