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

5 (1)

5 (1)