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..
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
17 People are following this question.
