SQL Server convert decimal to EBCDIC

Question

Anyone dealt with writing SQL Server database to EBCDIC and know if there is a function that will perform the below more efficiently?

It's easy to just complete the below for the remaining overpunch values. I tried searching at Microsoft's site and came up empty. CONVERT and CAST functions are a no go.

Simple wordy manual conversion which will work:

declare @origValue decimal(10, 4) = -11.20;

with cte_1 as 
(
    select right(cast(@origValue * 100 as smallint), 1) as digit 
)
select
    case when @origValue > 0 then case when digit = '0' then '{' end
         when @origValue < 0 then case when digit = '0' then '}' end
    end
from cte_1

Hopefully there's a more efficient method.

Note: I'm committed to a decimal(10, 4) type and will only be using up to hundredths position hence the conversion.

Answer 1

You can use the following function to convert the entire value to an ASCII representation of zoned decimal of various sizes and scales.

CREATE FUNCTION ToZonedDecimalASCII(@Value DECIMAL(38,19), @Size INT, @Scale INT)
RETURNS VARCHAR(50)
AS
BEGIN
    DECLARE @ScaledValue DECIMAL(38,0) = FLOOR(ABS(@Value) * Power(10., @Scale))
    DECLARE @Result VARCHAR(50) = CAST(@ScaledValue AS VARCHAR(50))
    SET @Result = RIGHT(REPLICATE('0', @Size) + @Result, @Size)
    SET @Result =
        STUFF(@Result, @Size, 1, '')
        + TRANSLATE(
            RIGHT(@Result, 1),
            '0123456789',
            IIF(@Value >= 0, '{ABCDEFGHI', '}JKLMNOPQR')
        )
    RETURN @Result
END

Note that the input size and precision is a compromise that should cover most common cases. Alternate versions for huge numbers or tiny fractions could also be defined.

Conversion of the results from ASCII to EBCDIC can be accomplished using the following limited translation that only covers the zoned-decimal character subset,

TRANSLATE(
    AsciiZonedDecimalValue,
    '{ABCDEFGHI' + '}JKLMNOPQR' + '0123456789',
    CAST(0xC0C1C2C3C4C5C6C7C8C9 +
         0xD0D1D2D3D4D5D6D7D8D9 +
         0xF0F1F2F3F4F5F6F7F8F9
         AS CHAR(30))
)

Results (with some extra test values and an EBCDIC/Hex translation):

Value	Size	Scale	AsciiResult	EbcdicHexResult
0.0000000000000000000	1	0	{	0xC0
555.0000000000000000000	5	0	0055E	0xF0F0F5F5C5
-666.0000000000000000000	5	1	0666}	0xF0F6F6F6D0
777.0000000000000000000	5	2	7770{	0xF7F7F7F0C0
3.1415926535897931160	10	5	000031415I	0xF0F0F0F0F3F1F4F1F5C9
-1.4142135623730951455	16	15	141421356237309N	0xF1F4F1F4F2F1F3F5F6F2F3F7F3F0F9D5
-11.2000000000000000000	8	2	0000112}	0xF0F0F0F0F1F1F2D0

See this db<>fiddle for a demo.

ADDENDUM

The above version has some limitations that fail for cases requiring more than 19 whole number digits and have loss of precision after more than 13 fractional digits.

The following is a more robust implementation that accepts an character representation of the number to be converted. It supports extreme ranges of DECIMAL(38,0) to DECIMAL(38,38) (and beyond) and even floating point exponential notation values.

This version also supports negative scale factors and virtually unlimited size and scale values.

CREATE FUNCTION ToZonedDecimalASCII(@Value VARCHAR(MAX), @Size INT, @Scale INT)
RETURNS VARCHAR(MAX)
AS
BEGIN
    -- If scientific notation detected, trim and apply exponent to scale
    DECLARE @EPos INT = CHARINDEX('E', @Value)
    IF @EPos > 0
    BEGIN
        SET @Scale = @Scale + CAST(STUFF(@Value, 1, @EPos, '') AS INT)
        SET @Value = LEFT(@Value, @EPos - 1)
    END

    -- Split whole and fractional portions and remove +/- sign (if present)
    DECLARE @Pos INT = CHARINDEX('.', @Value)
    DECLARE @Fract VARCHAR(MAX) = IIF(@Pos > 0, STUFF(@Value, 1, @Pos, ''), '')
    DECLARE @Whole VARCHAR(MAX) = IIF(@Pos > 0, LEFT(@Value, @Pos - 1), @Value)
    SET @Whole = REPLACE(REPLACE(@Whole, '+', ''), '-', '')

    -- Scale and pad
    DECLARE @Padded VARCHAR(MAX)
    IF @Scale >= 0
    BEGIN
        -- Zero or positive scale 
        SET @Fract = LEFT(@Fract + REPLICATE('0', @Scale), @Scale)
        DECLARE @Scaled VARCHAR(MAX) = @Whole + @Fract
        SET @Padded = RIGHT(REPLICATE('0', @Size) + @Scaled, @Size)
    END
    ELSE
    BEGIN
        -- Negative scale (or exponent): Drop fraction. Left-pad and right-trim whole number part.
        DECLARE @ExtendedSize INT = @Size + (-@Scale)
        SET @Padded = RIGHT(REPLICATE('0', @ExtendedSize) + @Whole, @ExtendedSize)
        SET @Padded = LEFT(@Padded, @Size)
    END

    -- Set zone code for last digit
    DECLARE @Result VARCHAR(MAX) =
        LEFT(@Padded, @Size - 1)
        + TRANSLATE(RIGHT(@Padded, 1), '0123456789',
            IIF(CHARINDEX('-', @Value) > 0, '{ABCDEFGHI', '}JKLMNOPQR')
        )

    RETURN @Result
END

Note: When used with floating point values, an explicit conversion using CONVERT(VARCHAR, @value, 3) is recommended to preserve maximum precision. The default floating point conversions yield a very limited precision.

See this db<>fiddle for a demo.