I need a function that checks if a GUID (provided as a varchar parameter) is valid.
The function should returns a UNIQUEIDENTIFIER if the GUID is valid, otherwise NULL
The function should permit GUIDs that have no hyphens (a form we tend to use in links in EMails etc. so that they don't get mucked up by hyphens being word-wrapped or URL-encoded. Thus the function is both a test and a "cleanup"
OK, here's my shot:
--
PRINT 'Create function fn_IsGUID'
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[fn_IsGUID]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP FUNCTION dbo.fn_IsGUID
GO
CREATE FUNCTION dbo.fn_IsGUID
(
@strGUID varchar(8000) -- String to be tested - hyphens optional, allows surrounding {...} and trims spaces
)
RETURNS uniqueidentifier -- NULL = Bad GUID encountered, else cleanedup GUID returned
/* WITH ENCRYPTION */
AS
/*
* fn_IsGUID Check that a String is a valid GUID
*
* Returns:
*
* Valid GUID (as uniqueidentifier), or NULL if invalid
*/
BEGIN
DECLARE @uidGUID uniqueidentifier
SELECT @strGUID = LTRIM(RTRIM(REPLACE(REPLACE(@strGUID, '{', ''), '}', ''))),
@strGUID = CASE WHEN LEN(@strGUID) = 32
THEN LEFT(@strGUID, 8)
+ '-' + SUBSTRING(@strGUID, 9, 4)
+ '-' + SUBSTRING(@strGUID, 13, 4)
+ '-' + SUBSTRING(@strGUID, 17, 4)
+ '-' + SUBSTRING(@strGUID, 21, 12)
ELSE @strGUID
END,
@uidGUID = CASE WHEN @strGUID like
'[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]'
THEN CONVERT(uniqueidentifier, @strGUID)
ELSE NULL
END
RETURN @uidGUID
/** TEST RIG
-- Good!
SELECT dbo.fn_IsGUID('BFF14A46-19F4-4E8E-BFE1-579E7ABDA3C1')
SELECT dbo.fn_IsGUID(' BFF14A46-19F4-4E8E-BFE1-579E7ABDA3C1 ')
SELECT dbo.fn_IsGUID(' {BFF14A46-19F4-4E8E-BFE1-579E7ABDA3C1} ')
SELECT dbo.fn_IsGUID(' {BFF14A4619F44E8EBFE1579E7ABDA3C1} ')
SELECT dbo.fn_IsGUID('bff14a46-19f4-4e8e-bfe1-579e7abda3c1')
-- Bad!
SELECT dbo.fn_IsGUID('BFF14A46-19F4-4E8E-BFE1-579E7ABDA3Cx')
SELECT dbo.fn_IsGUID('BFF14A461-9F4-4E8E-BFE1-579E7ABDA3C1')
SELECT dbo.fn_IsGUID('BFF14A46-19F44-E8E-BFE1-579E7ABDA3C1')
SELECT dbo.fn_IsGUID('BFF14A46-19F4-4E8EB-FE1-579E7ABDA3C1')
SELECT dbo.fn_IsGUID('BFF14A46-19F4-4E8E-BFE15-79E7ABDA3C1')
**/
--==================== fn_IsGUID ====================--
END
GO
PRINT 'Create function fn_IsGUID DONE'
GO
--
If a procedure works instead of a function. Something like this should work pretty easily. Sadly it doesn't work as a function as you can't have TRY/CATCH blocks in a function.
create procedure IsGuid(@input varchar(50), @output as uniqueidentifier output)
AS
BEGIN
DECLARE @NewString as varchar(50)
IF len(@input) = 32
BEGIN
SET @NewString = SUBSTRING(@input, 1, 8) + '-' + SUBSTRING(@input, 9, 4) + '-' +
SUBSTRING(@input, 13, 4) + '-' + SUBSTRING(@input, 13, 4) + '-' + SUBSTRING(@input, 20, 12)
END
ELSE
BEGIN
SET @NewString = @input
END
BEGIN TRY
SET @Output = @NewString
END TRY
BEGIN CATCH
SET @Output = NULL
END CATCH
END
GO
/*Test values*/
declare @o uniqueidentifier
exec dbo.IsGuid '8059C7A2-D360-4285-90C2-D02237027A15', @o output
select @o
exec dbo.IsGuid'8059C7A2D360428590C2D02237027A15', @o output
select @o
exec dbo.IsGuid'8059C7A2D360428590C2D02237027A5', @o output
select @o
CREATE FUNCTION dbo.fn_IsGUID(@GUID varchar(38))
RETURNS uniqueidentifier
AS
BEGIN
SET @GUID = STUFF(STUFF(STUFF(STUFF(REPLACE(REPLACE(REPLACE(REPLACE(@GUID,'-',''),' ',''),'}',''),'{','')
,9,0,'-'),14,0,'-'),19,0,'-'),24,0,'-')
BEGIN TRY
SELECT CAST(@GUID AS uniqueidentifier)
END TRY
BEGIN CATCH
SELECT NULL
END CATCH
END
