question

Kristen avatar image
Kristen asked

Best way to check that a (string/varchar) GUID is valid?

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"

guid
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kristen avatar image
Kristen answered

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            
--            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

MladenPrajdic avatar image
MladenPrajdic answered

I've played with that exact problem. See my solution here.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mrdenny avatar image
mrdenny answered

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            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder avatar image
Scot Hauder answered
            
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            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.