[dba-SQLServer]Is the data a GUID?

create function dbo.fn_IsGUID (
   @value varchar(8000)       ) returns int
as begin
   declare @pattern varchar(8000), @RetVal int
   set @pattern = replicate('[A-F-0-9]', 8)+'-'
   set @pattern = @pattern +
replicate('[A-F-0-9][A-F-0-9][A-F-0-9][A-F-0-9]-', 3)
   set @pattern = @pattern + replicate('[A-F-0-9]', 12)
   if @value like @pattern
      set @RetVal = 1
   else
      set @RetVal = 0
   return @RetVal
   end
go
select dbo.fn_IsGUID(cast(cast(newid() as char(36)) as char(35)))

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
Francisco H Tapia
Sent: Friday, November 14, 2003 12:57 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer]Is the data a GUID?


thanks for the reply, I'm going to need to somehow figure how to test 
this in Tsql tho, unfortunatly the GUID concept is one that I MUST use 
because of a replication model I'm following.

I don't want to digress to that conversation as I imagine many will ask,

and I'll leave too many confused as it won't be the first time the topic

has been brought up.

so I'm thinking how I'd check for this, maybe initially the lenght of 
the string?


MartyConnelly wrote:

> I don't know if there is anything specific beyond the fact that is a
> random 16 byte binary number
> and if the 13'th half byte is 4 it was generated by Microsoft (after 
> they stopped using the MAC address to create it), so not truly random.
> There is a guid datatype in vb.dotnet maybe check that documentation.
> 
> I suppose if you have it displayed as a hex string you could
> with/without those extraneous hyphens you could use regexp to check
> but how you do that in T-SQL, I dunno.
> 
> regExp.pattern="[{][A-F0-9]{8}-[A-F0-9]{4}-[A-F0-9]{4}-[A-F0-9]{4}-[A-
> F0-9]{12}[}]"
> 
> 
> 
> 
> Here is an interesting article on the speed of GUIDs with SQL that 
> might
> have further info.
> The Cost of GUIDs as Primary Keys
> MAR 08, 2002 By Jimmy Nilsson.
> 
> 
> http://www.informit.com/isapi/product_id~{E3D24CE5-F2A0-4B16-A39C-7AB1
>
7525F07C}/session_id~{DC1DFD4E-98B7-4E40-B3B5-DE36CA8F9FEF}/content/inde
x.asp
> 
> 
> A GUID is a 128-bit integer.  That's only 16 bytes.
> So your 12 byte value would be 2 to the 96'th power or  2^96
> 
> One interesting bit. GUIDs are not as random as advertised.
> 
> Listing 3: Some GUIDs Generated with SQL Server 7 on NT4
> 
> B3BFC6B1-05A2-11D6-9FBA-00C04FF317DF
> B3BFC6B2-05A2-11D6-9FBA-00C04FF317DF
> B3BFC6B3-05A2-11D6-9FBA-00C04FF317DF
> B3BFC6B4-05A2-11D6-9FBA-00C04FF317DF
> B3BFC6B5-05A2-11D6-9FBA-00C04FF317DF
> 
> Listing 4: Some GUIDs Generated with SQL Server 2000 on Windows 2000
> 
> C87FC84A-EE47-47EE-842C-29E969AC5131
> 2A734AE4-E0EF-4D77-9F84-51A8365AC5A0
> 70E2E8DE-500E-4630-B3CB-166131D35C21
> 15ED815C-921C-4011-8667-7158982951EA
> 56B3BF2D-BDB0-4AFE-A26B-C8F59C4B5103
> 
> "As you saw in Listing 3, only the eighth half byte is changed between
> calls. On the other hand, in Listing 4, only the 13th half byte is 
> constant between calls. In Windows 2000, the MAC address isn't used
any 
> longer for when GUIDs are generated. Instead, the GUID is only a
16-byte 
> random number. Well, that isn't totally true. The 13th half byte is 
> constant, so only 15.5 bytes are random. Adam Nathan at Microsoft 
> explained to me that the 13th half byte is the value that will point
out 
> the source of the GUID, and 4 means Microsoft."
> 
> 
> 
> Francisco H Tapia wrote:
> 
>> Anybody have a TSQL test on weather the data is a GUID or not? 
>> thanks....
>>
>> I want to test some data, I have the function for ISnumeric which
>> gives back a simple 1 or 0 if data is numeric or not, but does not 
>> tell you if the data is a GUID (or that it qualifies as a number).
>>
>> Thanks,
>>
> 


-- 
-Francisco

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com