Ordering the SQL UNIQUEIDENTIFIER Type Numerically Correct for Reporting
Jun 14 2009
As a follow-up to my last post on How SQL Server Sorts the UNIQUEIDENTIFIER Type, I thought it would be useful to have a function that would reorder the bytes of UNIQUEIDENTIFIERS whenever I need to show them in numerically correct order. Here's the User-Defined Function (UDF) I wrote to do this:
-- =============================================
-- Author: W. Kevin Hazzard
-- Create date: 14 June 2009
-- Description: Reorder the bytes of a
-- UNIQUEIDENTIFIER to show it as
-- a numerically correct string.
-- =============================================
CREATE FUNCTION [dbo].[NumericallyCorrectUid]
(
@uid UNIQUEIDENTIFIER
)
RETURNS NCHAR(36)
AS
BEGIN
DECLARE @result NCHAR(36)
SET @result = CONVERT(NCHAR(36), @uid)
SET @result =
SUBSTRING(@result, 25, 8)
+ N'-'
+ RIGHT(@result, 4)
+ SUBSTRING(@result, 19, 6)
+ SUBSTRING(@result, 17, 2)
+ SUBSTRING(@result, 15, 2)
+ N'-'
+ SUBSTRING(@result, 12, 2)
+ SUBSTRING(@result, 10, 2)
+ SUBSTRING(@result, 7, 2)
+ SUBSTRING(@result, 5, 2)
+ SUBSTRING(@result, 3, 2)
+ LEFT(@result, 2)
RETURN @result
END
GO
Invoking the UDF is easy. Here's a little script that demonstrates how to do it:
DECLARE @uid UNIQUEIDENTIFIER
SET @uid = N'EBC23DE8-DC16-4A8B-8E11-6B1509B0DAED'
PRINT @uid
DECLARE @correctUid NCHAR(36)
SELECT @correctUid = [<your DB name here>].[dbo].[NumericallyCorrectUid] (@uid)
PRINT @correctUid
This outputs the following text in the Messages window of SQL Server Management Studio:
EBC23DE8-DC16-4A8B-8E11-6B1509B0DAED
6B1509B0-DAED-8E11-8B4A-16DCE83DC2EB
This matches the octet ordering that I showed in the previous blog post. Of course, if you attempt to create a new UNIQUEIDENTIFER in SQL using the reordered string, the new value will not be equal to the orginal. So be careful to use the reordered string only for reporting or whenever you need to see the UNIQUEIDENTIFIER values in numerically correct order. Enjoy.
