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.

just a row of characters and numbers is really not a valid GUID representation - you'll have to resort to string parsing magic like Quassnoi showed in his answer. — marc_s, Sep 07 '09 at 17:07

score 150 · Accepted Answer · answered Sep 07 '09 at 16:36

150

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)

answered Sep 07 '09 at 16:36

Quassnoi

413,100
91
616
614

16

I was really hoping this would not be the solution but I guess we'll find out soon... – grenade Sep 07 '09 at 16:39
Placing this snippet in a function is a nice addition to your toolkit, especially since some JSON serializers remove the dashses from GUIDs when serializing, making it harder to copy paste into SQL to debug. – David Cumps Jan 23 '13 at 09:41
Almost 14 years later and this is still the solution? – StoneGiant Apr 24 '23 at 21:14
1

@StoneGiant: if, like the op, you don't want to do the conversion on the client side, then yes, that's the solution. If you're fine with doing this on the client side, any half-decent ORM can map this string to a .NET `Guid` either out of the box or by using a one-liner map rule. – Quassnoi Apr 25 '23 at 01:14
@JuanAcosta: I'm not really following what "remove the @ in the code" means and why you are doing it. What you posted will not parse as valid TSQL. – Quassnoi Jun 22 '23 at 04:04

Hafthor · Answer 2 · 2022-08-05T15:37:50.473

30

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

or a one-liner:

cast(stuff(stuff(stuff(stuff(replace(replace(@s,'0x',''),'-',''),21,0,'-'),17,0,'-'),13,0,'-'),9,0,'-') as uniqueidentifier)

edited Aug 05 '22 at 15:37

answered Jun 25 '13 at 23:52

Hafthor

16,358
9
56
65

5

Excellent use of Stuff(). I only need to reference my field once in a Select statement using your method. I avoid Scalar-Functions though, because they don't always quite "scale" well, so I write it out. Thanks, this is going in my Code Snippits! – MikeTeeVee Jun 27 '13 at 01:38

manji · Answer 3 · 2009-09-07T16:43:39.090

21

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))

edited Sep 07 '09 at 16:43

answered Sep 07 '09 at 16:37

manji

47,442
5
96
103

score 17 · Answer 4 · answered Jul 19 '16 at 10:15

17

SELECT CONVERT(uniqueidentifier,STUFF(STUFF(STUFF(STUFF('B33D42A3AC5A4D4C81DD72F3D5C49025',9,0,'-'),14,0,'-'),19,0,'-'),24,0,'-'))

answered Jul 19 '16 at 10:15

Matthew

211
2
3

score 1 · Answer 5 · edited Sep 02 '22 at 08:15

1

SELECT CAST(CAST('A89B1ACD-9501-6AE6-B9C8-AABB07DA2010' as char(36)) as uniqueidentifier)

edited Sep 02 '22 at 08:15

Ilya Kharlamov

3,698
1
31
33

answered Jul 19 '22 at 14:48

mccrhodes

11
1

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jul 20 '22 at 13:06

score 0 · Answer 6 · answered Jun 20 '23 at 03:46

I don't know how new a version of SQL Server you'd need for the following to work, but in current versions you can do the following...

DECLARE @myid uniqueidentifier ;  
SET @myid = 'A972C577-DFB0-064E-1189-0154C99310DAAC12';

This example is taken from Microsoft's documentation: https://learn.microsoft.com/en-us/sql/t-sql/functions/newid-transact-sql?view=sql-server-ver16

score -7 · Answer 7 · edited Aug 19 '19 at 10:51

-7

The guid provided is not correct format(.net Provided guid).

begin try
select convert(uniqueidentifier,'a89b1acd95016ae6b9c8aabb07da2010')
end try
begin catch
print '1'
end catch

edited Aug 19 '19 at 10:51

ChrisM

505
6
18

answered Jan 13 '15 at 09:52

user3082965

1
1

9

How does this answer the question of converting a varchar without hyphens to a GUID? All this code does is print 1. – Aaroninus Sep 11 '15 at 15:44

Convert varchar to uniqueidentifier in SQL Server

7 Answers7

Linked

Related