- Home
- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Site Issues (NOT FOR DATABASE QUESTIONS)
- Explore
- Topics
- Questions
- Users
- Badges

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
Comment

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.

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.

**20** People are following this question.

Copyright 2019 Redgate Software.
Privacy Policy