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!