[dba-SQLServer]Is the data a GUID?

ok, this has been modified, this is like your example I just noticed... :D

 declare @s sql_variant
 SELECT  @s = TESTtext FROM #TEST
select
 case when CAST(@s AS varchar(36)) like
( 
replicate('[0-9A-F]',8)+'-'+replicate(replicate('[0-9A-F]',4)+'-',3)+replicate('[0-9A-F]',12) 
)
collate Latin1_General_BIN
then 'Is GUID'
else 'Is not GUID'
end

Djabarov, Robert wrote:

>Yeah, except if the value happens to be put into a non-uniqueidentifier
>variable or field, - the result will show the datatype of that
>variable/field, not of the value.  Previously posted function resolves
>this issue.
>
>-----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 11:41 AM
>To: dba-sqlserver at databaseadvisors.com
>Subject: Re: [dba-SQLServer]Is the data a GUID?
>
>
>Here is the answer to my question, I received this on the Microsoft 
>newsgroup (sql server programming )
>
> > declare @s sql_variant
> > set @s = newid()
> > select sql_variant_property(@s,N'BaseType')
>
>this effectively returns the data type and that is what I can use to 
>validate if the data is a guid or NOT.  simply like the "IsNumeric" 
>funciton.
>
>I hope this helps out someone else :)
>
>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/in
>>dex.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