Post reply

Varchar to Varbinary

  • January 14, 2005 at 8:32 am

    #163636

    I have a flat file which has hex values in it. I need to bring this in and process the data so that I can retrieve the integer value of the value. Trouble is that to load the data into a table I have to bring it in as a varchar and massage it to get the hex value clean.

    Once I have the hex value in the varchar column I can do nothing else with it to get the conversion done. I've tried cast, convert, even dumping the data out and trying to bring it back in to no avail.

    Any thoughts?


    Shamless self promotion - read my blog http://sirsql.net

  • January 14, 2005 at 8:40 am

    #537488

    I never manage to get deeper into why this is, but consider this:

    DECLARE @hex VARCHAR(10)

    DECLARE @stmt NVARCHAR(255)

    DECLARE @int INT

    SET @hex = '0x0000008A'

    SELECT @stmt = N'SELECT @int = CONVERT( int , ' + @hex + ' )'

    EXEC sp_ExecuteSql @stmt, N' @int Int Out', @int OUT SELECT @int

    GO

    SELECT CAST(CAST('0x0000008A' AS VARBINARY) AS INT)

               

    -----------

    138

               

    -----------

    808466497

     

    The first brings the correct result, while the second doesn't. If anyone has an explanation, why, I would love do hear it.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • January 14, 2005 at 8:46 am

    #537491

    The second one converts the literal string into the binary value before returning it as an integer.


    Shamless self promotion - read my blog http://sirsql.net

  • January 14, 2005 at 9:07 am

    #537496

    The first one works like a charm however.

    I've changed the int to a bigint (as it's storing the date a milliseconds since 1970).

    Thanks Frank


    Shamless self promotion - read my blog http://sirsql.net

  • January 20, 2005 at 2:54 pm

    #538406

    A newbie question...

    I have a follow-up question to this thread. I'm new to SQL programming, and am also trying to convert hex numbers in a flat file into integers. What I don't understand is how to use the code above to process all the numbers in my table, not just one via the

    SET @hex = '0x0000008A' line.

    How would the code above be modified to perform the conversion on every record in a table?

  • January 20, 2005 at 7:48 pm

    #538432

    You could us a cursor to accomplish that, however it would be slow as it's a row by row process.


    Shamless self promotion - read my blog http://sirsql.net

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply