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
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/16/2005 :  01:58:16  Show Profile  Reply with Quote
Is there a better way to check that a GUID is valid?

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
)
RETURNS bit	-- True or False
AS
/*
 * fn_IsGUID	Check that a String is a valid GUID
 *
 * Returns:
 *
 * 	True / False
 */
BEGIN

DECLARE	@blnIsGUID	bit

	SELECT	@strGUID = LTRIM(RTRIM(@strGUID)),
		@blnIsGUID = 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 1
			ELSE 0
			END
	RETURN @blnIsGUID

/** TEST RIG

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-579E7ABDA3Cx')

**/
--==================== fn_IsGUID ====================--
END
GO
PRINT 'Create function fn_IsGUID DONE'
GO
--

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2050 Posts

Posted - 08/16/2005 :  03:36:57  Show Profile  Reply with Quote
Sounds like a job for a regular expression though I have no idea how you would achieve that



steve

Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/16/2005 :  04:31:22  Show Profile  Visit spirit1's Homepage  Reply with Quote
i do

create function IsGuid ( @testString varchar(36))
returns int
as
begin
declare @ret int
if len(@testString) = 36 and not exists(select 1 where @testString like '%[^0-9^A-F^a-f^-]%')
	set @ret = 1
else
	set @ret = 0
return @ret
end
go
--EAB1575F-BCB8-4935-9364-5140F64EE5F7
select dbo.IsGuid ('dAB1,7 F-BCB8-4935-9364-5140F64EE5F7')
select dbo.IsGuid ('dAB1df7F-BCB8-4935-9364-5140F64EE5F7')
select dbo.IsGuid ('dAB1s7F-BCB8-4935-9364-5140F64EE5F7 ')
go
drop function dbo.IsGuid 


EDIT:
a little correction:
'%[^0-9^A-F^a-f\^-]%' -> '%[^0-9^A-F^a-f^-]%'

Go with the flow & have fun! Else fight the flow

Edited by - spirit1 on 08/16/2005 05:58:17
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/16/2005 :  04:44:33  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
For the same string, I get different results

SELECT dbo.fn_IsGUID(' BFF14A46-19F4-4E8E-BFE1-579E7ABDA3C1 ') -- Kristen method returns 1
SELECT dbo.IsGUID (' BFF14A46-19F4-4E8E-BFE1-579E7ABDA3C1 ') -- Mladen method returns 0

Which is correct?


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/16/2005 :  04:50:30  Show Profile  Visit spirit1's Homepage  Reply with Quote
your answer lies in:
SELECT cast(' BFF14A46-19F4-4E8E-BFE1-579E7ABDA3C1 ' as uniqueidentifier)

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

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/16/2005 :  05:07:19  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by spirit1

your answer lies in:
SELECT cast(' BFF14A46-19F4-4E8E-BFE1-579E7ABDA3C1 ' as uniqueidentifier)

Go with the flow & have fun! Else fight the flow



Didnt you get this error?

Server: Msg 8169, Level 16, State 2, Line 1
Syntax error converting from a character string to uniqueidentifier.


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/16/2005 :  05:08:24  Show Profile  Visit spirit1's Homepage  Reply with Quote
exactly.
which means mine is correct, no?

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

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/16/2005 :  05:12:12  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Really yours is nice answer

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 08/16/2005 :  09:08:27  Show Profile  Reply with Quote
small issue with spirit1's:

are these guids?
select dbo.IsGuid ('E5059F566914394334BA7B1a0A598972F788')
select dbo.IsGuid ('------------------------------------')

Be One with the Optimizer
TG
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/16/2005 :  09:10:31  Show Profile  Visit spirit1's Homepage  Reply with Quote
i knew there were conditions i didn't test
nothing a few if's can't handle...

EDIT:

create function IsGuid ( @testString varchar(36))
returns int
as
begin
declare @ret int
if len(@testString) = 36 and not exists(select 1 where @testString like '%[^0-9^A-F^a-f\^-]%')
        and charindex('-', @testString) = 9
	and charindex('-', @testString, 10) = 14
	and charindex('-', @testString, 15) = 19
	and charindex('-', @testString, 20) = 24
	and charindex('-', @testString) in (9, 14, 19, 24)
	set @ret = 1
else
set @ret = 0
return @ret
end


EDIT1 in bold!


Go with the flow & have fun! Else fight the flow

Edited by - spirit1 on 08/16/2005 09:29:30
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 08/16/2005 :  09:13:22  Show Profile  Reply with Quote
I have all the confidence in the world in you spirit1, I just didn't want anyone to deploy faulty code

Be One with the Optimizer
TG
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/16/2005 :  09:14:19  Show Profile  Visit spirit1's Homepage  Reply with Quote
dude.... when did you go past 900??? just 30 to go...

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

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 08/16/2005 :  09:15:36  Show Profile  Reply with Quote
I have a version that I'm using currently although I wasn't too pleased with the looping. Since I'm only checking one at a time it works well, but I'll be happy to replace it with a more efficient version:

If Object_ID('dbo.fnIsGuid') > 0
	Drop Function dbo.fnIsGuid
GO

Create Function dbo.fnIsGuid(@Guid varChar(128)) returns bit

as

Begin
	--Guid format: '22D6CE78-8DBF-426D-8911-337A7277665D'

	declare @i tinyint

	--if first and last characters are curly braces
	--get rid of them
	set @Guid = replace(replace(@Guid, '{', ''), '}', '')

	--uniqueidentifier converts to char(36)
	if len(isNull(@Guid,'')) <> 36
		return 0

	set @i = 1

	while @i < 37 
	Begin
		if @i IN (9,14,19,24)
		Begin
			if subString(@Guid, @i, 1) <> '-'
				return 0
		End
		else if charindex(subString(@Guid, @i, 1), '0123456789ABCDEF') = 0
			return 0

		set @i = @i + 1
	End
	return 1
End


Be One with the Optimizer
TG

Edited by - TG on 08/16/2005 09:18:56
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 08/16/2005 :  09:17:29  Show Profile  Reply with Quote
>>dude.... when did you go past 900??? just 30 to go...

Woooo Hoooo!!! I'm just trying to reach 1000 without royally pissing anyone off. maybe I'm too late

Be One with the Optimizer
TG
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/16/2005 :  09:35:03  Show Profile  Visit spirit1's Homepage  Reply with Quote
who did you piss off?

ok final version... hopefully:

create function IsGuid ( @testString varchar(38))
returns int
as
begin
declare @ret int
set @testString = replace(replace(@testString, '{', ''), '}', '')
if len(isnull(@testString, '')) = 36 and not exists(select 1 where @testString like '%[^0-9^A-F^a-f^-]%')
	and charindex('-', @testString) = 9
	and charindex('-', @testString, 10) = 14
	and charindex('-', @testString, 15) = 19
	and charindex('-', @testString, 20) = 24
	set @ret = 1
else
	set @ret = 0
return @ret
end


so TG... will it do?

Go with the flow & have fun! Else fight the flow

Edited by - spirit1 on 08/16/2005 09:36:44
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 08/16/2005 :  09:42:04  Show Profile  Reply with Quote
ohh, so close!

select dbo.IsGuid ('37AD28CB-A3D4-4EB9-89AB-------------')

>>who did you piss off?
I'm trying to keep all negative attitude out of my posts (as well as my mind - but that is much harder)

EDIT:
this testing of other people's code is a very nice change of pace. I could go into qc and really start pissing people off. :)

Be One with the Optimizer
TG

Edited by - TG on 08/16/2005 09:44:41
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/16/2005 :  09:45:38  Show Profile  Visit spirit1's Homepage  Reply with Quote
this is cool

create function IsGuid ( @testString varchar(38))
returns int
as
begin
declare @ret int
set @testString = replace(replace(@testString, '{', ''), '}', '')
if len(isnull(@testString, '')) = 36 and not exists(select 1 where @testString like '%[^0-9^A-F^a-f^-]%')
	and charindex('-', @testString) = 9
	and charindex('-', @testString, 10) = 14
	and charindex('-', @testString, 15) = 19
	and charindex('-', @testString, 20) = 24
	and charindex('-', @testString, 25) = 0
	set @ret = 1
else
	set @ret = 0
return @ret
end


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

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/16/2005 :  09:46:41  Show Profile  Reply with Quote
Thanks folk.

Spirit: Can you replace

not exists(select 1 where @testString like '%[^0-9^A-F^a-f^-]%')

with

NOT LIKE '%[^0-9^A-F^a-f^-]%'

and if so would it be more efficient?

Do you need the embedded "^"? I''m not sure, but won't they match "^" themselves?

I think you need to be not testing for '-' in character positions where it is not expected - possibly something like comparing REPLACE(@testString, '-', '') against your RegEx, and then checking that "-" are all in the expected places.

SELECT dbo.fn_IsGUID(' BFF14A46-19F4-4E8E-BFE1-579E7ABDA3C1 ') -- Kristen method returns 1
SELECT dbo.IsGUID (' BFF14A46-19F4-4E8E-BFE1-579E7ABDA3C1 ') -- Mladen method returns 0


I was wanting to allow whitespace etc., but I now realise that the "caller" will also have to TRIM the value.

So perhaps it would be better to have this function return a GUID, or NULL if not valid, and then it could cleanup trailing space, and the possible "{...}" stuff.

And permit GUIDS that have no hyphens.

Kristen
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/16/2005 :  09:53:19  Show Profile  Visit spirit1's Homepage  Reply with Quote
yes you can replace the exists with like... i left it because i tested it that way
AFAIK ^ won't match themselves... they are the negating sign.

well you don't need to test it for all of the -'s but i did it to give TG some fun

TG:
that while of yours is simply mean

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

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 08/16/2005 :  09:55:12  Show Profile  Visit spirit1's Homepage  Reply with Quote
is GUID without hyphens still a guid? sql server wise not philosoficaly...


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

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/16/2005 :  10:03:09  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
and should all the alphabets be in Capital to be GUID?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next 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.12 seconds. Powered By: Snitz Forums 2000