Is there a way to convert IBM zoned decimal to SQL server decimal?

[achick]

I would like to convert a zoned deciamal (IBM's S9(9)V99 last character is overloaded) to SQL server numeric data type with decimals.

Can a query be used for it? or are there built in conversions that we can use?

 

[fredericofonseca]

Putting more here than what you really asked for - you or others may need it at a later stage.

For pure PIC S(9)V99 fields... e.g. no COMP-xxxxx, you have to worry about two aspects only.
1- sign
2- decimal places
The function below allows you to deal with both, but it is up to you do determine and supply the desired number of decimal places.

CREATE FUNCTION [dbo].[fnOverpunch]
       (@Overpunch VARCHAR(30)
       ,@Scale INT
       )

--As written, this function accepts a string and returns a float
-- To convert text fields in a text file, can return a varchar(30) by
-- changing the RETURNS and RETURN lines

-- --USAGE - signt in the right
-- SELECT dbo.fnOverpunch('123R', 2)
-- -- returns -12.39
-- SELECT dbo.fnOverpunch('123R', 0)
-- -- returns -1239
-- SELECT dbo.fnOverpunch('123D', 2)
-- -- returns 12.34
-- SELECT dbo.fnOverpunch('123D', 0)
-- -- returns 1234

-- --USAGE - signt in the left
-- SELECT dbo.fnOverpunch('R123', 2)
-- -- returns -91.23
-- SELECT dbo.fnOverpunch('R123', 0)
-- -- returns -9123
-- SELECT dbo.fnOverpunch('D123', 2)
-- -- returns 41.23
-- SELECT dbo.fnOverpunch('D123', 0)
---- returns 41
RETURNS FLOAT
 AS 
 BEGIN
    DECLARE @Result VARCHAR(30)
           ,@Char CHAR(1)
           ,@CharIndex INT
           ,@Multiplier INT
    SET @Overpunch = LTRIM(RTRIM(@Overpunch))
    IF @Overpunch IS NULL
       OR @Overpunch = ''
-- Function is done
       RETURN NULL
    IF ISNUMERIC(@Overpunch) = 1 
       BEGIN -- Numeric
             SET @Result = @Overpunch
             SET @Multiplier = 1
       END -- Numeric
    ELSE 
       BEGIN -- not numeric
             SET @Char = RIGHT(@Overpunch, 1)
             IF ISNUMERIC(@CHAR) = 0 
                BEGIN -- sign on the right
                      SET @Overpunch = LEFT(@Overpunch, LEN(@Overpunch) - 1)
                      SET @CharIndex = CHARINDEX(@Char, '{ABCDEFGHI')
                      IF @CharIndex > 0 
                         BEGIN
                               SET @Result = @Overpunch
                                   + CONVERT(CHAR(1), @CharIndex - 1)
                               SET @Multiplier = 1
                         END
                      SET @CharIndex = CHARINDEX(@Char, '}JKLMNOPQR' COLLATE Latin1_General_CS_AS) -- ebcdic representation
                          + CHARINDEX(@Char, 'pqrstuvwxy' COLLATE Latin1_General_CS_AS) -- ascii representation
                      
                      IF @CharIndex > 0 
                         BEGIN
                               SET @Result = @Overpunch
                                   + CONVERT(CHAR(1), @CharIndex - 1)
                               SET @Multiplier = -1
                         END
                END -- signt on the right
             ELSE 
                BEGIN -- sign on the left
                      SET @Char = LEFT(@Overpunch, 1)
                      SET @Overpunch = SUBSTRING(@Overpunch, 2,
                                                 LEN(@Overpunch) - 1)
                      SET @CharIndex = CHARINDEX(@Char, '{ABCDEFGHI')
                      IF @CharIndex > 0 
                         BEGIN
                               SET @Result = CONVERT(CHAR(1), @CharIndex - 1)
                                   + @Overpunch
                               SET @Multiplier = 1
                         END
                      SET @CharIndex = CHARINDEX(@Char, '}JKLMNOPQR') -- ebcdic representation
                          + CHARINDEX(@Char, 'pqrstuvwxy') -- ascii representation
                      IF @CharIndex > 0 
                         BEGIN
                               SET @Result = CONVERT(CHAR(1), @CharIndex - 1)
                                   + @Overpunch
                               SET @Multiplier = -1
                         END
                END -- signt on the right
       END -- not numeric
    IF @Scale = 0 
       BEGIN
             RETURN CAST(@Result AS FLOAT) * @Multiplier
       END
    SET @Result = LEFT(@Result, LEN(@Result) - @Scale) + '.' + RIGHT(@Result,
                                                              @Scale)
--if @Multiplier = -1 --Used to return a varchar(30) result
--set @Result = '-' + @Result
    RETURN CAST(@Result AS FLOAT) * @Multiplier
--return @Result --Used to return a varchar(30) result

 
 END

copied from [URL unfurl="true"]http://www.sqlservercentral.com/Forums/Topic207585-9-1.aspx[/URL]
and changed to allow for sign on the left side of the string (uncommon but used)
Full text placed here as links can get lost

For packed decimals
you can do it in C# (which can be used in SSIS) - If you need a pure T-sql solution I am unware of any.


If you are transferring this file from a mainframe the following applies.
If the file you are processing has a mix of number and char fields then you need to have a program (which can be a ssis c# script) that will perform the conversion from ebcdic to asci of the char fields. This same program should also do the conversion of the packed data based on a the field definition you will need to supply to this program on a per file basis (or even on a per record basis).
Note that the file has to be transferred in BINARY mode down to your pc - the ftp conversion will ruin your packed data if you use it.


If the file was created on a asci os (Windows/Unix) then you will only have to worry about the packed data field conversion

For binary fields (comp, binary, comp-x, comp-5) you will also need to take in consideration the byte alignment of the source OS.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[achick]

Thank you and thanks for the reference to the original article, that did work great.