[dba-SQLServer]Is the data a GUID?

Modified last time, this works well...
 DECLARE @s sql_variant, @RetVal as INT
 SELECT  @s = TESTtext FROM #TEST

IF 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
    BEGIN
        set @RetVal = 1
    END
ELSE
    BEGIN
        set @RetVal = 0
    END
RETURN @RetVal



Francisco H Tapia wrote:

> 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