question

viki2015 avatar image
viki2015 asked

Convert Decimal to Hex

Hi, I wanted to convert decimal into hex (string type) so that '3781220527799385' will look like '000D-6F00-0260-7C59'. can someone please help me to do the trick.
sql-server-2008sql
1 comment
10 |1200 characters needed characters left characters exceeded

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

You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered
You should be able to reverse the answer of your previous Hex to Decimal question to get the unformatted hex string. Then, I think it's a case of using the string functions to add a hyphen every 4 characters.
1 comment
10 |1200 characters needed characters left characters exceeded

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

I am not SQL expert and tried many things but did not work, I can do it through inbuit function same as in my last one but I am looking for a solution that can be applied in any environment
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered
I'm not going to write it for you but the steps would be something like this: Count how many whole groups of 4 characters there are. Let's call this n. I think you always get an even number of characters in hex but it's not guaranteed to be a multiple of 4 so, if the length of the string is longer than n * 4, add 1 to n. You will need to insert a hyphen every 4 characters n-1 times. In your example you have 16 characters, which is 4 groups of 4, so you need to insert 3 hyphens. Now use a WHILE loop to do the following n-1 times: Find the place for the next hyphen which will be (n*4) + (n-1) + 1. The n-1 is to allow for the hyphens you've already inserted. Let's call this position p. Insert the hyphen there by setting the variable to be (the left part of the string up to p) + '-' + (the rest of the string from p onwards). You can do these with the SUBSTR function. It's quite a lot of steps so you'll probably want to package it up as a function.
10 |1200 characters needed characters left characters exceeded

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

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.