March 6, 2009 at 4:46 pm
Hello all,
I have been in the process of transferring a database from Access 2007 to SQL Server 2005, and I'm at the point where I have to copy a few queries manually to SQL Server. I have one particular set of queries that returns a column with both the values from a column in a table and added to it the hex version of the same number. The code in Access SQL is as follows
SELECT
(mytable![column name]) & " (0x" & Hex(mytable[column name]) & ")" AS [newcolumn_name]
FROM
mytable
I've found that the HEX() command is not supported in T-SQL (from msdn) so I've tried many ways to get around this from around this forum and other sites but i havn't found a solution. I'm currently trying to use
cast(dbo.mytable.[column name] as varbinary(2)) AS [newcolumn_name]
but the returned values are not correct. The column in mytable is type varchar, does that have anything to do with it? I found this solution at a microsoft page but the value that was being called to cast was declared as "int." I'm assuming that my column will have to be of type int before this method will work, but that is not something I can change.
Any help on this topic would be much appreciated, especially if this conversion can be done within the query. I'm very new to SQL Server and T-SQL so please be descriptive.
Many Thanks,
Al
March 6, 2009 at 7:35 pm
Hi Al
If your binary is less than or equal to 10 bytes you can use the not documented Microsoft function fn_cdc_hexstrtobin 😉
SELECT master.sys.fn_cdc_hexstrtobin('AE01')
Greets
Flo
March 9, 2009 at 7:39 am
Flo,
Thanks for the quick response but this doesn't seem to be working. I might be using it wrong, but I copy/pasted your pointer to a general query and it returned
Msg 4121, Level 16, State 1, Line 1
Cannot find either column "master" or the user-defined function or aggregate "master.sys.fn_cdc_hexstrtobin", or the name is ambiguous.
I tried to mess around with it a bit but no luck. I'm using SQL Server 2005 Express if that makes a difference. Also, is "hex str to bin" going to convert a varchar datatype to a hex string?
Regards,
Al
March 9, 2009 at 8:51 am
Hi Al
Sorry, my misstake! It seems that this function is new in SQL Server 2008.
Greets
Flo
March 11, 2009 at 6:30 am
Have yet to find a working solution to this problem, anyone else have any ideas / pointers / vast amount of awesome knowledge on this issue? 😀
March 11, 2009 at 6:33 am
Hi
If you found a solution maybe post it (or a link) here so other people with same problem may get a solution too ;).
Greets
Flo
March 11, 2009 at 6:35 am
could you give me some data examples please 🙂
Also have you tried using: hashbytes
SORRY THIS WILL NOT WORK
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 11, 2009 at 6:45 am
here is an example of sql function that might do the trick:
SELECT master.dbo.fn_varbintohexstr(459)
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 11, 2009 at 8:04 am
Hi Christopher
That also was my first suggestion, but if you have a look he needs exactly the opposite like a of "fn_varbintohexstr" like a "fn_hexstrtovarbin" 🙂 . But this is not available in SQL Server since 2005.
Greets
Flo
March 11, 2009 at 8:20 am
ah I see, cool well if I get an example from him, it might be possible to write the code to do the opposite:-)
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 11, 2009 at 9:15 am
Here is exactly what I'm trying to do:
I have a column with various values, but for sake of shortness, one of those values is 65484.
In an Access 2007 query, this column is called and the hex value of that number is appended to it, resulting in
"65484 (0xFFCC)"
The code that does this in Access 2007 is
SELECT
(mytable![mycolumn]) & " (0x" & Hex(mytable![mycolumn]) & ")"
trying to reproduce this in SQL doesn't work because HEX() is not a function in SQL Server 2005.
mycolumn is type nvarchar
I want to just calculate the HEX value of that number within the query
Thanks for all the replies!
March 11, 2009 at 9:27 am
Ok here is my solution.
I've created a function which uses some of the logic from the SQL function I posted earlier only it removes excess 0000 etc.
CREATE FUNCTION dbo.fn_VarbinToHexStringChris
(
@int INT
)
--EXAMPLE
--SELECT dbo.fn_VarbinToHexStringChris(255)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @var VARBINARY(4)
--DECLARE @int INT
DECLARE @hexString CHAR(16)
DECLARE @STRING as VARCHAR(10)
SELECT
@hexString = '0123456789ABCDEF',
@var = CAST(@int as VARBINARY)
;WITH MyCTE (Row,Result)
AS
(SELECT
n,
CAST(SUBSTRING(@hexString,((CAST(SUBSTRING(@var,n,1) as INT)/16)+1),1) as CHAR(1)) +
CAST(SUBSTRING(@hexString,((CAST(SUBSTRING(@var,n,1) as INT)%16)+1),1) as CHAR(1)) as [str]
FROM dbo.Tally
WHERE n <= DATALENGTH(@var)
AND CAST(SUBSTRING(@var,n,1) as INT) !='')
SELECT @STRING = COALESCE(@STRING , '') + [Result]
FROM MyCTE
RETURN '0x' + @STRING
END
GO
Here is the code for testing your solution:
DECLARE @tbl TABLE
(MyNum INT)
INSERT INTO @tbl
SELECT 65484
SELECT
MyNum,
(SELECT dbo.fn_VarbinToHexStringChris(MyNum)),
CAST(MyNum as VARCHAR) + ' (' + (SELECT dbo.fn_VarbinToHexStringChris(MyNum)) + ')'
FROM @tbl
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 11, 2009 at 9:33 am
Thanks for the reply, I'm still a complete SQL Server newbie so it will take me a bit to get this to work, but I'll post success/failure as soon as I can. 😛
March 11, 2009 at 9:37 am
shouldn't be that hard.
Create the function
then replace my solution example, by changing the table name and field name to your table name and your field name, BAMB you should get the results 🙂
This line here :
SELECT dbo.fn_VarbinToHexStringChris(MyNum)
Return the same as your line of:
Hex(mytable![mycolumn])
hope this makes it easier
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 11, 2009 at 9:52 am
ah, it seems to be working ok, except that I have a value in "mycolumn" = 70 or 60 or 50" which doesn't successfully convert. Is there a way to write an if/else statement so that if a conversion fails, the query will still run and won't fail on me?
In access, the query will still generate a table of results and add ERROR in the spot where that error occurs where SQL seems to not generate results if there is a failure
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply