none
Need to read Binary Data as Little Endian instead of Big Endian

    Question

  •  

    Hi I hae a stored procedure that reads 2048 INT32's (4 bytes each) from a column of type image and returns all the values converted to decimal.  The problem I have found is that I am reading the data is if it was being stored as Big Endian, but it stored as Little Endian. 

     

    Does anybody know what I need to change in my SP to get it to read Little Endian?

     

    Code Snippet

    --get Particle EDS ID

    declare @ParticleEDSID bigint

    set @ParticleEDSID = (SELECT ParticleID FROM Particle WHERE RunID=@runID AND PartNum=@partNum)

     

    --This temp table will be the output

    create table #Output(RowID int identity(1,1), bin4 binary(4), iOutput as convert(int, bin4))

     

    --Variables that we'll need

    DECLARE @ptrval varbinary(16),

    @RowNum int,

    @ReadStart int,

    @cmd nvarchar(255),

    @Parms nvarchar(255),

    @DataLen bigint

     

    --Get the total len of the field (will be 2048 * 4)

    select @DataLen = DataLength(Spectrum)

    from ParticleEDS where ParticleEDSID = @ParticleEDSID

     

    set @RowNum = 0

     

    SELECT @ptrval = TEXTPTR(Spectrum)

    FROM ParticleEDS where ParticleEDSID = @ParticleEDSID

    set @parms = '@b binary(16)'

    BeginLoop:

    set @ReadStart = @RowNum * 4

    set @cmd = 'READTEXT ParticleEDS.Spectrum @b ' + convert(nvarchar,@ReadStart) + ' 4;'

    insert into #Output(bin4)

    exec sp_ExecuteSQL @cmd, @Parms, @ptrval

    set @RowNum = @RowNum + 1

    if (@RowNum * 4) < @DataLen

    goto BeginLoop

     

    select * from #OUtput

    drop table #Output

     

     

    Friday, June 06, 2008 6:15 PM

Answers

  • SQL Server doesn't really handle binary conversion very well.  I would suggest you get the source into the correct format befrore SQL see it.

    The code given will not work correctly for large numbers.  Take:

    0x02011234


    Depending on the source system this mgiht be:


    0x01023412

    or

    0x34120102

    or
    0x12340102





    Monday, June 09, 2008 2:05 PM
  • Here is my solution.  I just used a bunch of substrings:

     

     

    Code Snippet

    SELECT RowID, 

         dbo.HexStrToVarBin('0x' + substring(sys.fn_varbintohexstr(CAST(iOutput AS binary(4))),9,2) + substring(sys.fn_varbintohexstr(CAST(iOutput AS binary(4))),7,2) + substring(sys.fn_varbintohexstr(CAST(iOutput AS binary(4))),5,2) + substring(sys.fn_varbintohexstr(CAST(iOutput AS binary(4))),3,2) ) AS LITTLE_ENDIAN,

     

         CONVERT(INT,dbo.HexStrToVarBin('0x' + substring(sys.fn_varbintohexstr(CAST(iOutput AS binary(4))),9,2) + substring(sys.fn_varbintohexstr(CAST(iOutput AS binary(4))),7,2) + substring(sys.fn_varbintohexstr(CAST(iOutput AS binary(4))),5,2) + substring(sys.fn_varbintohexstr(CAST(iOutput AS binary(4))),3,2) )) as iOutput

        

    FROM #OUtput

     

     

     

    input (Big Endian): 0x02000000

     

    Output: LITTLE_ENDIAN: 0x00000002  ; iOutput: 2

    Monday, June 09, 2008 4:10 PM

All replies

  • So nobody here has ever encountered a problem such as this?

    I just seems as though if reading int32 from a binary field in T-SQL they must be in big endian format.

    May I am trying to do something is out of the scope of T-SQL.

    -David
    Saturday, June 07, 2008 6:25 PM
  • David:

     

    I am not completely sure of what I am aiming at, but I will try; if this is not heading in the right direction I am sorry.  I have not looked at big endian / little endian since 1997:

     

    Code Snippet

    declare @little binary(4) set @little = 0x02010000

     

    select
      @little [bigEndian ?],
      cast(reverse(@little) as binary(4)) [littleEndian ?],
      cast(cast(reverse(@little) as binary(4)) as integer) [targetInt ?]

     

    /* -------- Sample Output: --------
    bigEndian ? littleEndian ? targetInt ?
    ----------- -------------- -----------
    0x02010000  0x00000102     258
    */

     

    Sunday, June 08, 2008 1:11 PM
  • Thanks Kent,

    That appears that it may help me out.  I was not familiar with the reverse() function.  I do not have access to the DB here at home so I will have to wait until Monday morning.

    -David
    Sunday, June 08, 2008 8:12 PM
  • Very good, David.  Please let me know how it works out.  Also, are you interested in obaining a copy of SQL 2005 Express for home?

    Sunday, June 08, 2008 9:12 PM
  • SQL Server doesn't really handle binary conversion very well.  I would suggest you get the source into the correct format befrore SQL see it.

    The code given will not work correctly for large numbers.  Take:

    0x02011234


    Depending on the source system this mgiht be:


    0x01023412

    or

    0x34120102

    or
    0x12340102





    Monday, June 09, 2008 2:05 PM
  • I finally got this with a combination of the reverse function the the undocumented t-sql function sys.fn_varbintohexstr(), and the function dbo.HexStrToVarBin()(gotten from Peter DeBetta's SQL Programming BLOG )

     

    This code snippet assumes that you have column @bin4 of type hex (BIG_ENDIANT format) in a temporary table (#OUtput).

     

    Code Snippet

     

    SELECT RowID, 

         dbo.HexStrToVarBin('0x' + REVERSE(substring(sys.fn_varbintohexstr(CAST(iOutput AS binary(4))), 3, 8))) AS LITTLE_ENDIAN,

         CONVERT(INT,dbo.HexStrToVarBin('0x' + REVERSE(substring(sys.fn_varbintohexstr(CAST(iOutput AS binary(4))), 3, 8)))) as iOutput

    FROM #OUtput

     

     

     

     

    Monday, June 09, 2008 2:13 PM
  • I found out that my output was really incorrect for example:

     

    if I had the hex string: 0x12345678 it should convert to 0x78563412 but it was converting to 0x87654321 therefore my interger conversions were way off.

     

    It should be fairly easy and straightforward to get my desired output. 

     

    I will post when I have got this complete.

    Monday, June 09, 2008 2:26 PM
  • Here is my solution.  I just used a bunch of substrings:

     

     

    Code Snippet

    SELECT RowID, 

         dbo.HexStrToVarBin('0x' + substring(sys.fn_varbintohexstr(CAST(iOutput AS binary(4))),9,2) + substring(sys.fn_varbintohexstr(CAST(iOutput AS binary(4))),7,2) + substring(sys.fn_varbintohexstr(CAST(iOutput AS binary(4))),5,2) + substring(sys.fn_varbintohexstr(CAST(iOutput AS binary(4))),3,2) ) AS LITTLE_ENDIAN,

     

         CONVERT(INT,dbo.HexStrToVarBin('0x' + substring(sys.fn_varbintohexstr(CAST(iOutput AS binary(4))),9,2) + substring(sys.fn_varbintohexstr(CAST(iOutput AS binary(4))),7,2) + substring(sys.fn_varbintohexstr(CAST(iOutput AS binary(4))),5,2) + substring(sys.fn_varbintohexstr(CAST(iOutput AS binary(4))),3,2) )) as iOutput

        

    FROM #OUtput

     

     

     

    input (Big Endian): 0x02000000

     

    Output: LITTLE_ENDIAN: 0x00000002  ; iOutput: 2

    Monday, June 09, 2008 4:10 PM
  • There must be an easier way? Does anyone know a more efficient method of reversing a group of hex values?
    Wednesday, April 08, 2009 11:01 PM