April 19, 2011 at 1:41 pm
Hi everyone.
This query gives me the correct result:
select CONVERT(bigint,0x80074e21)
I'm trying to do the same thing with values from a table that are stored as varchar(50). I simulated the situation with this:
select CONVERT(bigint,cast(0x80074e21 as varchar(50)))
Unfortunately it does not work.
Error converting data type varchar to bigint.
I can't find anything to cast the varchar(50) data as so that it comes through the convert with the same result as the top.
Any suggestions?
Thanks much.
Howard
April 19, 2011 at 1:46 pm
When you convert that hex to varchar, I'm getting:
€N!
which doesn't convert to an int.
Do you mean to be doing this?
select CONVERT(VARCHAR(50), CONVERT(bigint,0x80074e21))
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 19, 2011 at 3:04 pm
Thanks for replying.
I am not trying to convert that hex to varchar. I am trying to convert that hex to bigint using a field that stores the hex as varchar(50).
The first line above outputs the query result that I am looking for.
2147962401
The second line simulates what the output is when the input comes from a varchar(50) column.
I want to do this:
select CONVERT(bigint,<column name that is varchar>)
and get the correct output.
I assume that I have to cast the varchar stored hex as some other data type before converting it but I don't know what type to use.
Thanks.
April 19, 2011 at 3:24 pm
have you tried varbinary?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 19, 2011 at 3:39 pm
yes and I get a completely different number.
/*------------------------
select CONVERT(bigint,0x80074e21)
------------------------*/
--------------------
2147962401
(1 row(s) affected)
/*------------------------
select distinct CONVERT(bigint,cast([status] as varbinary)) as converted, [status]
FROM [FWS_000].[dbo].[FWLog]
where status = '0x80074e21'
------------------------*/
converted status
-------------------- --------------------------------------------------
4048789078666457649 0x80074e21
(1 row(s) affected)
So in the second query, status is the varchar(50) input and converted should match the output in the first query.
April 20, 2011 at 3:58 pm
With a UDF such as the one in this article...
http://sqlblog.com/blogs/peter_debetta/archive/2007/03/09/t-sql-convert-hex-string-to-varbinary.aspx **
...you can then do stuff like this:
SELECT CAST(dbo.HexStrToVarBin('0x80074e21') AS BIGINT) ;
** Note that I am not vouching for this particular implementation of a UDF that can change hex-strings to varbinary, just that the technique may work for you.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 20, 2011 at 4:22 pm
2008 can do this natively with CONVERT options.
The trick is to convert to VARBINARY in the middle with the style: 1
Here's some sample code:
DECLARE @hexStr VARCHAR(20),
@hexBin varBINARY(20)
SET @hexStr = '0x4158'
SET @hexBin = 0x4158
PRINT @hexStr
PRINT @hexBin
-- What we want:
PRINT CONVERT( BIGINT, @hexBin)
-- This doesn't help, but it's what we think we want.
PRINT CONVERT( BIGINT, CONVERT( VARBINARY(20), @hexStr))
-- Here's the key
PRINT CONVERT( BIGINT, CONVERT( VARBINARY(20), @hexStr, 1))
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 20, 2011 at 4:29 pm
Craig Farrell (4/20/2011)
2008 can do this natively with CONVERT options.The trick is to convert to VARBINARY in the middle with the style: 1
Here's some sample code:
DECLARE @hexStr VARCHAR(20),
@hexBin varBINARY(20)
SET @hexStr = '0x4158'
SET @hexBin = 0x4158
PRINT @hexStr
PRINT @hexBin
-- What we want:
PRINT CONVERT( BIGINT, @hexBin)
-- This doesn't help, but it's what we think we want.
PRINT CONVERT( BIGINT, CONVERT( VARBINARY(20), @hexStr))
-- Here's the key
PRINT CONVERT( BIGINT, CONVERT( VARBINARY(20), @hexStr, 1))
Slick :smooooth:
Now that you point it out I remember reading an Itzik article talking about the new feature in 2008. Here is the article: http://www.sqlmag.com/article/tsql3/sql-server-2008-s-t-sql-features/2
I don't use VARBINARY enough to have remembered reading about the improvement!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 20, 2011 at 4:33 pm
Thanks both of you for the replies.
This should do exactly what is needed. For some reason, the output has to be a negative number so I took what is generated and * -1
Really appriciate the help!
Howard
May 11, 2011 at 11:35 am
Ooops...wrong thread.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 8, 2011 at 4:48 pm
Hi Craig, your example using style 1 worked perfectly in the other file. The new one I am working with is slightly different.
Here is my example:
DECLARE @hexStr VARCHAR(20),
@hexBin varBINARY(20)
SET @hexStr = '0x180'
SET @hexBin = 0x180
PRINT @hexStr
PRINT @hexBin
-- What we want:
PRINT CONVERT( BIGINT, @hexBin)
-- Does not work with the above varchar
PRINT CONVERT( BIGINT, CONVERT( VARBINARY(20), @hexStr, 1))
--Seems that putting an extra 0 after the x is required.
PRINT CONVERT( BIGINT, CONVERT( VARBINARY(20), REPLACE(@hexStr,'x','x0'), 1))
Does that make sense? I don't like the replace method because it is altering the data. Can SQL Server handle a value like 0x180?
July 8, 2011 at 4:56 pm
PHXHoward (7/8/2011)
Does that make sense? I don't like the replace method because it is altering the data. Can SQL Server handle a value like 0x180?
The question makes sense, and you're right, you're altering the data... but the data's coming to you in an invalid format. Binary values need to come in pairs. Any binary with an odd # of characters is literally invalid according to the strict definition. Under the hood, SQL fixes the 'real' binary, but the converter doesn't know what the heck to do with the string. For proof of the 'under the hood fix', check out the print of the @hexBin.
This falls under the 'data scrubbing' portion of importing, and with binary values would scare the demons out of me. I would go back to the file issuer and make them give you valid binaries.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 8, 2011 at 5:08 pm
Thanks Craig, the print of the @hexbin was how I figured out to add the 0. I did not realize the issue was odd vs. even numbers but that makes perfect sense.
The data I am getting is the .w3c output of logging being done by ISA Server for Microsoft's Forefront Threat Management Gateway.
We need it in text format so that it can be pulled into another tool but then I need to load it into SQL Server for reporting.
Using all the sample data I have to work with, the conversion seems to be working now but I see what you are saying about invalid source data.
Thanks again.
Howard
July 9, 2011 at 11:52 am
opc.three (4/20/2011)
Slick :smooooth:Now that you point it out I remember reading an Itzik article talking about the new feature in 2008. Here is the article: http://www.sqlmag.com/article/tsql3/sql-server-2008-s-t-sql-features/2
Not only is it slick, it is extremely fast - hundreds or even thousands of times faster than a user-defined function.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 12, 2011 at 4:51 pm
Why do these give me different results? 0x80 has an even number of characters.
select CONVERT (bigint, convert(VARBINARY(20),'0x80') ,1)
select CONVERT (bigint, 0x80 ,1)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply