My Resume

  • My Resume (MS Word) My Resume (PDF)


Affiliations

  • Microsoft Most Valuable Professional
  • INETA Community Champion
  • Leader, NJDOTNET: Central New Jersey .NET User Group

Friday, November 2, 2007

Safe Handling of Uniqueidentifier Using T-SQL

I'm not a DBA, so I'll be the first to admit I am no expert in SQL. But, I do like to think I know my fair share and can write relatively efficient and effective SQL pretty easily.

That being said, I ran into some trouble today. I am doing web hit tracking, and to avoid losing any data, I'm storing environment variables (customer ids, download ids, and other assrt'd custom data) of varying types. As such, I'm storing these values as the nvarchar(MAX) type so I can convert them to whatever I want later while avoiding a lot of the type-casting errors at run-time.

Problem is, when I went to go insert some of this data into a temp table to work with, I kept getting this error when trying to convert a column that should have been filled with good uniqueidentifiers values (albeit stored as an nvarchar type):

Conversion failed when converting from a character string to uniqueidentifier.

OH NO! Well, first I had to figure out what value was giving me trouble. After a bit of searching, I found out that it was one like this: BE92BFCE-A425-4FXA-85X2-AAX4C7C92AAE. Can you see it? Yeah - 'X' is not a valid character in a UUID! Looks like someone was submitting wacky values into my tracking system... Well, I had already accepted the fact that I would get invalid values, but I certainly didn't want them to get in the way of processing my valid ones. So, I scoured the 'net for a "ToGUID" function that would safely fail when passed in a bad value like the one above and, surprisingly enough, I came up with NOTHING! I couldn't even really find any articles or forum posts on the most effective way to parse and validate a uniqueidentifier! I decided I had to write my own methods, and what I came up with were two UDFs: one to validate whether or not the string is a valid uniqueidentifier and the other to actually convert it... gracefully. That means you can pass it anything, and it'll either give you back a uniqueidentifier or NULL! No errors! Here's the code:


IsValidGuid(nvarchar(MAX))
CREATE FUNCTION dbo.[IsValidGuid](@input NVARCHAR(MAX))
RETURNS bit
AS
BEGIN
DECLARE @isValidGuid BIT;
SET @isValidGuid = 0;
SET @input = UPPER(LTRIM(RTRIM(REPLACE(@input, '-', ''))));

IF(@input IS NOT NULL AND LEN(@input) = 32)
BEGIN
DECLARE @indexChar NCHAR(1)
DECLARE @index INT;
SET @index = 1;
WHILE (@index <= 32)
BEGIN
SET @indexChar = SUBSTRING(@input, @index, 1);
IF (ISNUMERIC(@indexChar) = 1 OR @indexChar IN ('A', 'B', 'C', 'D', 'E', 'F'))
SET @index = @index + 1;
ELSE
BREAK;   
END

IF(@index = 33)
SET @isValidGuid = 1;
END

RETURN @isValidGuid;
END
ToGuid(nvarchar(MAX))
CREATE FUNCTION dbo.[ToGuid](@input NVARCHAR(MAX))
RETURNS UNIQUEIDENTIFIER
AS
BEGIN
DECLARE @guid UNIQUEIDENTIFIER;
SET @guid = NULL;

-- If this is a valid GUID, try to convert it
IF(dbo.[IsValidGuid](@input) = 1)
BEGIN
DECLARE @guidString AS NVARCHAR(MAX);
SET @guidString = UPPER(LTRIM(RTRIM(REPLACE(@input, '-', ''))));
SET @guidString = STUFF(@guidString, 9, 0, '-')
SET @guidString = STUFF(@guidString, 14, 0, '-')
SET @guidString = STUFF(@guidString, 19, 0, '-')
SET @guidString = STUFF(@guidString, 24, 0, '-')

IF(@guidString IS NOT NULL)
SET @guid = CONVERT(UNIQUEIDENTIFIER, @guidString);
END

RETURN @guid;
END

EDIT: As Sloan mentions in his comment below, all of the STUFF lines above can be abbreviated together into one line, so you end up with something like this: select @guidString = STUFF(STUFF(STUFF(STUFF(UPPER(LTRIM(RTRIM(REPLACE( @guidString, '-', '')))) , 9, 0, '-'), 14, 0, '-') , 19, 0, '-') , 24, 0, '-')

So you can just call "dbo.ToGuid(foo)" and it'll convert foo to a GUID for you or return a NULL in its place. As you can see, ToGuid() calls IsValidGuid() to validate the input, but you can still use IsValidGuid() where appropriate as well.

This seems to be working for me. Hope it helps you as well!

14 comments:

Harrison said...

Thanks!

To really fix my bug I need to take my DataSet.MyTable.MyColumn....and make MyColumn a Guid (its a string now).
But you're code helped me put in a production time fix...without recompile.

I made the fix into 1 line (using your code of course).

select @myguid =
STUFF(STUFF(STUFF(STUFF(UPPER(LTRIM(RTRIM(REPLACE( @myVarChar , '-', '')))) , 9, 0, '-'), 14, 0, '-') , 19, 0, '-') , 24, 0, '-')

For a quick fix.

I appreciate the post.

Jess Chadwick said...

Thanks for your comment, Sloan - I'm glad I could help you out! Not having to recompile to make a production fix is always a good thing. :)

Alex Palilonis said...

I like how you decided to abbreviate assorted.

Anonymous said...

Incredible... A year later this post still invaluable. Outstanding!

Jess Chadwick said...

Thanks for your comment, EN. I love to hear when my posts are helping someone!

Anonymous said...

Thanks for the functions! I just ran into the need to validate a uniqueidentifier.

shaymax said...

Great work man.

Anonymous said...

What can I say! You probably saved my professional life with this code! Sharepoint relies on unique identifiers.
just remember the curlie braces
string sId = "{AB9479D2-935B-4F65-9EEC-441713B4BF1E}" is something othere than "'AB9479D2-935B-4F65-9EEC-441713B4BF1E'"

Jess Chadwick said...

Anonymous,
Oh yeah - I've been getting into more Sharepoint development lately and I can definitely say I've used this for my Sharepoint stuff more than a couple of times. :)

rissky said...

nicely done ^_^

Edge-L1-k said...

Thanks for the code. This is very helpful. And saved me at least a few hours of coding.

Rob said...

Man this saved me a lot of work. Thanks so much!

John Skrotzki ( Partner: Source Recreation ) said...

Thanks!

Unknown said...

Your IsValidGuid function is awesome. It just resolved for me a major data integrity issue for my team that we were running into loading data from one system to another.

Thank you.