Convert varchar to uniqueidentifier in SQL Server

Question

A table I have no control of the schema for  contains a column defined as varchar 50  which stores uniqueidentifiers in the format  a89b1acd95016ae6b9c8aabb07da2010   no hyphens   I want to convert these to uniqueidentifiers in SQL for passing to a  Net Guid  However  the following query lines don t work for me   select cast  a89b1acd95016ae6b9c8aabb07da2010  as uniqueidentifier  select convert uniqueidentifier   a89b1acd95016ae6b9c8aabb07da2010     and result in   Msg 8169  Level 16  State 2  Line 1 Conversion failed when converting from a character string to uniqueidentifier   The same queries using a hyphenated uniqueidentifier work fine but the data is not stored in that format   Is there another  efficient  way to convert these strings to uniqueidentifiers in SQL  -- I don t want to do it in the  Net code

User · Accepted Answer

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

User · Answer

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

User · Answer

The guid provided is not correct format  net Provided guid    begin try select convert uniqueidentifier  a89b1acd95016ae6b9c8aabb07da2010   end try begin catch print  1  end catch

User · Answer

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

User · Answer

SELECT CONVERT uniqueidentifier STUFF STUFF STUFF STUFF  B33D42A3AC5A4D4C81DD72F3D5C49025  9 0  -   14 0  -   19 0  -   24 0  -

User · Answer

If your string contains special characters you can hash it to md5 and then convert it to a guid uniqueidentifier   SELECT CONVERT UNIQUEIDENTIFIER  HASHBYTES  MD5             a89b1acd95016ae6b9c8aabb07da2010

[sql] Convert varchar to uniqueidentifier in SQL Server

Examples related to sql

Examples related to sql-server-2005

Examples related to uniqueidentifier