Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 IsGUID

Author  Topic 

Kristen
Test

22859 Posts

Posted - 2005-08-16 : 01:58:16
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
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-08-16 : 03:36:57
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

11752 Posts

Posted - 2005-08-16 : 04:31:22
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-16 : 04:44:33
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

11752 Posts

Posted - 2005-08-16 : 04:50:30
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

22864 Posts

Posted - 2005-08-16 : 05:07:19
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

11752 Posts

Posted - 2005-08-16 : 05:08:24
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

22864 Posts

Posted - 2005-08-16 : 05:12:12
Really yours is nice answer

Madhivanan

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-16 : 09:08:27
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

11752 Posts

Posted - 2005-08-16 : 09:10:31
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
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-16 : 09:13:22
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

11752 Posts

Posted - 2005-08-16 : 09:14:19
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-16 : 09:15:36
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
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-16 : 09:17:29
>>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

11752 Posts

Posted - 2005-08-16 : 09:35:03
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
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-16 : 09:42:04
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
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-16 : 09:45:38
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

22859 Posts

Posted - 2005-08-16 : 09:46:41
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

11752 Posts

Posted - 2005-08-16 : 09:53:19
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

11752 Posts

Posted - 2005-08-16 : 09:55:12
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

22864 Posts

Posted - 2005-08-16 : 10:03:09
and should all the alphabets be in Capital to be GUID?

Madhivanan

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

- Advertisement -