In SQL Server, I need to pack 2 characters into 1 character, similar to HEX. How?

I have a SQL Server table that has a column in it that is defined as Binary(7). It is updated with data from a Cobol program that has Comp-3 data (packed decimal). I wrote a C# program to take a number and create the Comp-3 value. I have it available to SQL Server via CLR Integration. I'm able to access it like a stored procedure.

My problem is, I need to take the value from this program and save it in the binary column. When I select a row of data that is already in there, I am seeing a value like the following:

0x00012F0000000F

The value shown is COBOL comp-3 (packed decimal) data, stored in the SQL table. Remember, this field is defined as Binary(7). There are two values concatenated and stored here. Unsigned value 12, and unsigned value 0.

I need to concatenate 0x00012F (length of 3 characters) and 0x0000000F (length of 4 characters) together and write it to the column.

My question is two part.

1) I am able to return a string representation of the Comp-3 value from my program. But, I'm not sure if this is the format I need to return to make this work. What format should I return to SQL, so it can be used correctly?

2) What do I need to do to convert this to make it work?

I hope I was clear enough. It's a lot to digest...Thanks!

I figured it out! I needed to change the output to byte[], and reference it coming out of the program in SQL as varbinary.

This is the code, if anyone else in the future needs it. I hope this helps others that need to create Comp-3 (packed decimal) in SQL. I'll outline the steps to use it below.

Below is the source for the C# program. Compile it as a dll.

using System;
using System.Collections.Generic;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

namespace Numeric2Comp3
{
//PackedDecimal conversions

public class PackedDecimal
{

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void ToComp3(string numberin, out byte[] hexarray, out string hexvalue)
    {

        long value;
        bool result = Int64.TryParse(numberin, out value);

        if (!result)
        {
            hexarray = null;
            hexvalue = null;
            return;
        }

        Stack<byte> comp3 = new Stack<byte>(10);

        byte currentByte;
        if (value < 0)
        {
            currentByte = 0x0d;     //signed -
            value = -value;
        }
        else if (numberin.Trim().StartsWith("+"))
        {
            currentByte = 0x0c;     //signed +
        }
        else
        {
            currentByte = 0x0f;     //unsigned 
        }

        bool byteComplete = false;
        while (value != 0)
        {
            if (byteComplete)
                currentByte = (byte)(value % 10);
            else
                currentByte |= (byte)((value % 10) << 4);
            value /= 10;
            byteComplete = !byteComplete;
            if (byteComplete)
                comp3.Push(currentByte);
        }
        if (!byteComplete)
            comp3.Push(currentByte);
        hexarray = comp3.ToArray();
        hexvalue = bytesToHex(comp3.ToArray());
    }

    private static string bytesToHex(byte[] buf)
    {
        string HexChars = "0123456789ABCDEF";
        System.Text.StringBuilder sb = new System.Text.StringBuilder((buf.Length / 2) * 5 + 3);
        for (int i = 0; i < buf.Length; i++)
        {
            sbyte b = Convert.ToSByte(buf[i]);
            b = (sbyte)(b >> 4);     // Hit to bottom
            b = (sbyte)(b & 0x0F);   // get HI byte
            sb.Append(HexChars[b]);
            b = Convert.ToSByte(buf[i]);             // refresh
            b = (sbyte)(b & 0x0F);   // get LOW byte
            sb.Append(HexChars[b]);
        }
        return sb.ToString();
    } 

} 
}

Save the dll somewhere in a folder on the SQL Server machine. I used 'C:\NTA\Libraries\Numeric2Comp3.dll'.

Next, you'll need to enable CLR Integration on SQL Server. Read about it on Microsoft's website here: Introduction to SQL Server CLR Integration. Open SQL Server Management Studio and execute the following to enable CLR Integration:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Once that is done, execute the following in Management Studio:

CREATE ASSEMBLY Numeric2Comp3 from 'C:\NTA\Libraries\Numeric2Comp3.dll' WITH PERMISSION_SET = SAFE

You can execute the following to remove the assembly, if you need to for any reason:

drop assembly Numeric2Comp3

Next, in Management studio, execute the following to create the stored procedure to reference the dll:

CREATE PROCEDURE Numeric2Comp3
@numberin nchar(27), @hexarray varbinary(27) OUTPUT, @hexstring nchar(27) OUTPUT
AS
EXTERNAL NAME Numeric2Comp3.[Numeric2Comp3.PackedDecimal].ToComp3

If everything above runs successfully, you're done!

Here is some SQL to test it out:

DECLARE @in nchar(27), @hexstring nchar(27), @hexarray varbinary(27)
set @in = '20120123'
EXEC Numeric2Comp3 @in, @hexarray out, @hexstring out

select len(@hexarray), @hexarray

select len(@hexstring), @hexstring

This will return the following values:

(No column name)    (No column name)
5                   0x020120123F

(No column name)    (No column name)
10                  020120123F                 

In my case, what I need is the value coming out of @hexarray. This will be written to the Binary column in my table.

I hope this helps others that may need it!