January 14, 2005 at 8:32 am
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?
January 14, 2005 at 8:40 am
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
The second one converts the literal string into the binary value before returning it as an integer.
January 14, 2005 at 9:07 am
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
January 20, 2005 at 2:54 pm
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
You could us a cursor to accomplish that, however it would be slow as it's a row by row process.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply