Menu
-- If you want to generate a new Guid (uniqueidentifier) in SQL server the you can simply use the NEWID() function. -- This will return a new random uniqueidentifier e.g. You can directly use this with INSERT statement to insert new row in table.
The globally unique identifier (GUID) data type in SQL Server is represented by the uniqueidentifier data type, which stores a 16-byte binary value. A GUID is a binary number, and its main use is as an identifier that must be unique in a network that has many computers at many sites.
There are two functions using which you can create GUIDs in SQL Server – NewID and NewSequentialID. And there's a data type – "uniqueidentifier" which can be used to store GUIDs. It stores a 16-btye binary value.
DECLARE @uuid VARCHAR(50)
SET @uuid = 'a89b1acd95016ae6b9c8aabb07da2010'
SELECT CAST(
SUBSTRING(@uuid, 1, 8) + '-' + SUBSTRING(@uuid, 9, 4) + '-' + SUBSTRING(@uuid, 13, 4) + '-' +
SUBSTRING(@uuid, 17, 4) + '-' + SUBSTRING(@uuid, 21, 12)
AS UNIQUEIDENTIFIER)
It would make for a handy function. Also, note I'm using STUFF instead of SUBSTRING.
create function str2uniq(@s varchar(50)) returns uniqueidentifier as begin
-- just in case it came in with 0x prefix or dashes...
set @s = replace(replace(@s,'0x',''),'-','')
-- inject dashes in the right places
set @s = stuff(stuff(stuff(stuff(@s,21,0,'-'),17,0,'-'),13,0,'-'),9,0,'-')
return cast(@s as uniqueidentifier)
end
your varchar col C:
SELECT CONVERT(uniqueidentifier,LEFT(C, 8)
+ '-' +RIGHT(LEFT(C, 12), 4)
+ '-' +RIGHT(LEFT(C, 16), 4)
+ '-' +RIGHT(LEFT(C, 20), 4)
+ '-' +RIGHT(C, 12))
SELECT CONVERT(uniqueidentifier,STUFF(STUFF(STUFF(STUFF('B33D42A3AC5A4D4C81DD72F3D5C49025',9,0,'-'),14,0,'-'),19,0,'-'),24,0,'-'))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With