Num2Guid Conversion Function - SQL, database, informix, oracle, SQL Server

Article,describing,convert,decimal,guid,forma

SQL Stats

Code: 25,719 lines
Jobs: 420 postings

Sponsored by:

You are in:

Latest Code Ticker for SQL.

Validate Email
By Lewis Moten on 6/22

IsAlphaNumeric
By Lewis Moten on 6/22

ValidFilename
By Lewis Moten on 6/22

Job to script all Jobs
By Srdjan Josipovic on 6/19

Enumerate SQL Servers using SQLDMO and T-SQL
By Srdjan Josipovic on 6/19

Count Colums
By Usman Farhat on 6/19

Easy travel SQL logins
By Zoltan Tamas, Toth on 6/17

Build a sequence via derived tables
By Michael S. Trachtenberg on 6/15

Creating Grids
By Lewis Moten on 6/15

(Screen Shot)

Click here to put this ticker on your site!

Add this ticker to your desktop!

Daily Code Email

To join the 'Code of the Day' Mailing List click here!

Affiliate Sites

Num2Guid Conversion Function

	Print
	Email

Submitted on: 5/24/2002 7:23:12 PM
By: Lewis Moten
Level: Advanced
User Rating: Unrated
Compatibility:Other

Users have accessed this article 480 times.

(About the author)

Article describing how to convert a decimal to a guid format. Also goes into detail explaining how to convert to other base-N numbers such as a bitmask/binary, octet, trinary, hex, and more. Introduces the benefit of functions with a real-case scenario.

This article has accompanying files

Terms of Agreement:
By using this article, you agree to the following terms...
1) You may use this article in your own programs (and may compile it into a program and distribute it in compiled format for langauges that allow it) freely and with no charge.
2) You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
3) You may link to this article from another website, but ONLY if it is not wrapped in a frame.
4) You will abide by any additional copyright restrictions which the author may have placed in the article or article's description.

Num2GUID Conversion Function

Why is a User Defined Function (UDF) powerful? Take this statement into consideration:

UPDATE Users SET GUID = dbo.Num2GUID(ID)

Here is a classic example of someone who may be changing there database schema to use GUIDs in order to support scaling it to hold a much larger number of users. Had they been using a stored procedure, they would probably end up using a cursor to iterate through each individual record with the output of the stored procedure. On top of that, they would have to perform the same process or foreign keys within other tables as well. This is the primary reason that this function has come into existence.

I revamped my original Int2GUID function to handle hex conversions of base-10 integers. Below is an example of the differences between the two results when passing a value of 10.

Int2GUID: 00000000-0000-0000-0000-000000000010

Num2GUID: 00000000-0000-0000-0000-00000000000A

As you can see from the results, hex conversion will take less space as the numbers increase in value because you have 16 characters within each position rather then 10. I have also changed the data type being passed to a BIGINT rather then an INT alone. As SQL Server improves and functionality permits, this data type can be increased to handle larger numbers by simply changing the definition.

The algorithm contained within the function is very generic for handling many types of base conversion from decimal numbers (base 10) with the exception of hooks placed within the code for GUID formatting. You can define your base characters in the proper order at the top of the function. Most of the common base conversions I have used are as follows:

Type	Base	SET @BaseChars =
Binary/Bits	2	01
Octet	8	01234567
Decimal	10	0123456789
Hex	16	0123456789ABCDEF

If you wish to use other base conversions, then make sure that you change the data type being returned to either a varchar. If you wish to return an int, big int, or other numeric data type, you will also need to cast the @Result as the proper data type when returning the final result.

The other piece of code you will need to modify is where the hooks are for formatting the @Result as a GUID. The initial GUID conversion code is defined in this article. The original file and supporting files for the other conversion functions may be found within the supporting files that come with this article.

CREATE FUNCTION [dbo].[Num2GUID] (@Number BIGINT)

RETURNS UNIQUEIDENTIFIER AS

BEGIN

        /*

        Converts a base-10 number to a base-16 hexadecimal number.

        The number is returned in GUID format used with unique identifier

        Data types.

        */

        DECLARE @Result VARCHAR(36)

        DECLARE @BaseChars VARCHAR(16)

        DECLARE @Base TINYINT

        -- Setup base characters in appropriate positions

        SET @BaseChars = '0123456789ABCDEF'

-- Determine base conversion based on length of characters defined

        SET @Base = LEN(@BaseChars)

        -- Initialize guid

        SET @Result = ''

        WHILE NOT @Number = 0

        BEGIN

                -- Insert next hex character

                SET @Result = SUBSTRING(@BaseChars, (@Number - ((@Number / @Base) * @Base)) + 1,

1) + @Result

                -- Reduce number

                SET @Number = @Number / @Base

                -- HOOK to insert separator depending on length of Results

                SET @Result = CASE LEN(@Result)

                    WHEN 12 THEN '-' + @Result

                    WHEN 17 THEN '-' + @Result

                    WHEN 22 THEN '-' + @Result

                    WHEN 27 THEN '-' + @Result

                    ELSE @Result

                    END

        END

        -- Insert formatted padding

        SET @Result = LEFT('00000000-0000-0000-0000-000000000000', 36 - LEN(@Result)) + @Result

        Return(@Result)

END

Should you wish to learn more about the author of this document, Lewis Moten, Then you may visit http://www.lewismoten.com to find programming scripts in a variety of languages, artwork, stories, poetry, and more.

Download article

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. Afterdownloading it, you will need a program like Winzipto decompress it.

Virus note:All files are scanned once-a-day by Planet Source Code for viruses,but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:
1)Re-scan downloaded files using your personal virus checker before using it.
2)NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

If you don't have a virus scanner, you can get one at many places on the net including:McAfee.com

Other 25 submission(s) by this author

Report Bad Submission

Your Vote!

Other User Comments

There are no comments on this submission.

Add Your Feedback!

Note:Not only will your feedback be posted, but an email will be sent to the code's author in your name.

NOTICE: The author of this article has been kind enough to share it with you. If you have a criticism, please state it politely or it will be deleted.

For feedback not related to this particular article, please click here.