locked
Print Hex value as string without 0x RRS feed

  • Question

  • select CONVERT(varbinary(max), 729231826, 2), 
    	Substring(CONVERT(varbinary(max), 729231826, 2), 3, 8)

    I am using above convert statement to convert my number (729231826) to hex value  so i get result as

    0x2B7731D2

    I need to get rid of 0x.. so output should be

    2B7731D2 ..

    As u can see i tried substring but then my output is 0x31D2.

    Kindly Help.

    Thanks,

    Nilay


    Thanks, Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, June 21, 2013 2:58 PM

Answers

  • select convert(varchar(max), CONVERT(varbinary(max), 729231826, 2),2)

    2B7731D2

    (1 row(s) affected)


    David

    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Naomi N Friday, June 21, 2013 3:32 PM
    • Marked as answer by NS_NET Friday, June 21, 2013 3:39 PM
    Friday, June 21, 2013 3:30 PM

All replies

  • I'm afraid you will have to convert it into a string first before using a substring

    Geert Vanhove DCOD ------ http://geertvanhove.wordpress.com/ ----------- Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    Friday, June 21, 2013 3:01 PM
  • Try:

    upper(stuff(sys.fn_varbintohexstr(CONVERT(varbinary(max), 729231826, 2)),1,2,''))
    Friday, June 21, 2013 3:04 PM
  • I also tried

    substring(cast(CONVERT(varbinary(max), 729231826, 2) as varchar(12)), 3, 8), 

    output then is 10 some special character.

    Thanks, Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, June 21, 2013 3:06 PM
  • yes it does work with fn_varbintoHexStr function but since its undocumented function, want to use convert if possible.

    Thanks, Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, June 21, 2013 3:08 PM
  • sys.fn_varbintohexstr is an undocumented system function so you should not use it instead you can create CLR function for this.

    script for this is available here - http://social.msdn.microsoft.com/Forums/sqlserver/en-US/dddd957b-8561-479d-98f6-70585853ca85/sysfnvarbintohexstr-vs-clr-function

    Friday, June 21, 2013 3:21 PM
  • select convert(varchar(max), CONVERT(varbinary(max), 729231826, 2),2)

    2B7731D2

    (1 row(s) affected)


    David

    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Naomi N Friday, June 21, 2013 3:32 PM
    • Marked as answer by NS_NET Friday, June 21, 2013 3:39 PM
    Friday, June 21, 2013 3:30 PM