question

leo2015 avatar image
leo2015 asked

Convert query result from Dec to Hex

Hi, when I run SELECT C1 FROM T1 where C2 not in (SELECT C2 FROM T2) I get result something like: 3781220527756006 3781220577400149 3781220529277389 3781220539322535 3781220530726066 3781220527701149 3781220527794203 3781220515679666 3781220539322905 3781220518468058 Now I want all these to be shown like below: 000D6F0001AF5A6E 000D6F0001AF5ABE 000D6F0001D21C6C 000D6F00025FDA7A ... I used `SELECT CONVERT(VARBINARY(8), cast (3781220527757946 as bigint))` for conversion but in this way I will have to manually enter each result and this query again prefixs 0x before the hex value. Can someone please help me on this. Thanks in advance..
sql-server-2008sqlquery
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
KenJ avatar image
KenJ answered
it looks like you have all the pieces and just need a hand putting them together. You can use your convert statement right in the original query: select convert(varchar(30), convert(varbinary(8), c1), 2) from t1 Was the manual entry concern that you couldn't do this? To get rid of the 0x, I've wrapped your varbinary conversion in a varchar conversion using style 2. Check out the "binary styles" section of the cast and convert documentation - [ https://msdn.microsoft.com/en-us/library/ms187928.aspx][1] Here is the whole thing with sample data - it's hugely helpful if you can include this type of setup with your question: create table #t1 (c1 bigint) insert #t1 select 3781220527756006 union all select 3781220577400149 union all select 3781220529277389 union all select 3781220539322535 union all select 3781220530726066 union all select 3781220527701149 union all select 3781220527794203 union all select 3781220515679666 union all select 378122053932290 select c1 as bigintvalue, convert(varchar(30), convert(varbinary(8), c1), 2) as stringvalue, convert(varbinary(8), c1) as binaryvalue from #t1 drop table #t1 [1]: https://msdn.microsoft.com/en-us/library/ms187928.aspx
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

worked like a charm...thanks
0 Likes 0 ·
glad to help
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.