SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 IsGUID
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 08/16/2005 :  10:09:58  Show Profile  Reply with Quote
MHOs:

>>is GUID without hyphens still a guid? sql server wise not philosoficaly...
Sql server wise, if it won't cast to uniqueidentifier its not a guid.

>>and should all the alphabets be in Capital to be GUID?
no


The speed and accruacy winner replaces my "mean" while loop :)


if object_id('isGuidTest') > 0 drop table isGuidTest
if object_id('isGuidResults') > 0 drop table isGuidResults
go
create table isGuidTest (rowid int identity(1,1), guid varchar(50))
create table isGuidResults (rowid int, isGuid bit)
go
insert isGuidTest (guid)
select top 1000 teststr
from	(select newid() teststr) a
cross join master..sysobjects
union all
select top 1000 substring(convert(varchar,name),1,50) from master..sysobjects
go

truncate table isGuidResults
go
declare @st datetime
set @st = getdate()
insert isGuidResults (rowid, isGuid)
select rowid, dbo.IsGuid(guid)
from	isGuidTest
select datediff(millisecond, @st, getdate()) millisecs

select	maxRealGuid = (select max(rowid) from isGuidResults where isguid = 1)
	,minBadGuid = (select min(rowid) from isGuidResults where isguid = 0)



Be One with the Optimizer
TG
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 08/16/2005 :  10:11:45  Show Profile  Reply with Quote
"is GUID without hyphens still a guid?"

Nope, it will fail a CAST. But when we use them for external links (e.g. in EMails etc) we remov the hyphens as they tend to get mucked up - Emails word wrap, the hyphen needs to be URL-encoded, and such stuff, so I would like my "GUID cleanup function" to sort that out too.

"AFAIK ^ won't match themselves... they are the negating sign."

I thought the negating just had one "^" as the very first character of the [...] set - but BoL is not entirely clear

Kristen
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/16/2005 :  11:28:34  Show Profile  Visit spirit1's Homepage  Reply with Quote
well you could always have it return a table with
a hyphenless guid and the bit which says if it is or isn't a guid, no?

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 08/16/2005 :  15:00:26  Show Profile  Reply with Quote
"AFAIK ^ won't match themselves... they are the negating sign."

Just tried this one, which comes back "ok"

select dbo.IsGuid ('dAB1df7F-BCB8-4^35-9364-5140F64EE5F7')

Changing to @testString like '%[^0-9A-Fa-f]%') changes the result to "Bad"

Kristen
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 08/16/2005 :  15:05:18  Show Profile  Reply with Quote
Here's my latest attempt (note changed from True/False return code to returning a valid, cleaned-up, GUID, or NULL if invalid

Kristen

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

Kristen
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000