SQL Server: converting UniqueIdentifier to string in a case statement

Question

We have a log table that has a message column that sometimes has an exception stack trace. I have some criteria that determines if the message has this. We do not want to show these messages to the customer but instead have a message like:

Internal Error Occured. Contact US with reference code xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

where xxx etc is a guid column in the table. I am writing stored proc like this:

declare @exceptionCriteria nvarchar(50)
select @exceptionCriteria = '%<enter criteria etc>%'

select LogDate,
       case
       when Message like @exceptionCriteria
       then 'Internal Error Occured. Reference Code: ' + str(RequestID)
       else Message
       end
  from UpdateQueue

RequestID is a Guid datatype in SQL Server and does not convert to string here. I've seen some code on how to convert a Guid to string, but it is multi-lined and I don't think it would work in a case statement. Any ideas?

aarona · Accepted Answer · 2022-04-26T17:01:45.443

369

I think I found the answer:

convert(nvarchar(36), RequestID)

Here's the link where I found this info:

http://msdn.microsoft.com/en-us/library/ms187928.aspx

edited Apr 26 '22 at 17:01

answered Jun 09 '11 at 22:14

aarona

35,986
41
138
186

23

Alternatively, cast(RequestID as varchar(50)) – MK_Dev Jun 09 '11 at 22:17
4

What is the basic difference between cast() and convert() ? – R K Sharma Dec 04 '15 at 09:53
4

I don't know why I'd pick `nvarchar`, much less `nvarchar(50)`. A unique identifier, when converted to a text value is treated in hex-dash-36. – user2864740 Apr 22 '16 at 06:58
i use it in my view and nicely worked and send them for my friends in SQL communities . GUID seems to confuse me ;) – saber tabatabaee yazdi Aug 15 '16 at 15:52
18

As other answers says, you can condense this `cast(RequestID as char(36))`. – Frank Tan Oct 04 '16 at 17:13
Shouldn't we be uppercase CONVERT – P6345uk Apr 28 '21 at 08:27
@P6345uk: That's a stylistic preference, and not a functional distinction; SQL Server is case insensitive when it comes to function names. I always use capital functions and keywords in my scripts, but it's not strictly required. – Jeremy Caney Apr 25 '22 at 21:43

Philippe Grondier · Answer 2 · 2021-04-29T09:11:01.253

113

It is possible to use the convert function here, but 36 characters are enough to hold the unique identifier value:

convert(nvarchar(36), requestID) as requestID

Edit: yes, as noted in the comments, char, or nchar, or any function that can properly manipulate ASCII character tables would do the trick. Then, my excuse is that I usually work in a multilingual/multialphabet environment, and the rule is to go for nvarchar, always. That's my no-brainer way of doing things, sorry. And, if one of these days, some database software starts to generate unique identifier with non-ASCII elements, I will be ready.

edited Apr 29 '21 at 09:11

answered Jun 09 '11 at 22:18

Philippe Grondier

10,900
3
33
72

2

because length is fixed even varchar(36) is enough here – gdbdable Apr 20 '16 at 12:49
14

You say "fixed" and yet "varchar" in the same sentence ... how about `char(36)`? You might also use `nchar(36)`, but since a GUID doesn't contain unicode, it buys you nothing. Conversely, operations with `char` are generally faster than `varchar`. – r2evans Jun 09 '17 at 15:00
Shouldn't we be uppercase CONVERT – P6345uk Apr 28 '21 at 08:27

score 72 · Answer 3 · edited Nov 01 '18 at 21:58

72

In my opinion, uniqueidentifier / GUID is neither a varchar nor an nvarchar but a char(36). Therefore I use:

CAST(xyz AS char(36))

edited Nov 01 '18 at 21:58

d219

2,707
5
31
36

answered Sep 03 '15 at 11:21

Silvan Hofer

1,361
11
12

score 9 · Answer 4 · edited Jun 10 '11 at 04:49

9

Instead of Str(RequestID), try convert(varchar(38), RequestID)

edited Jun 10 '11 at 04:49

marc_s

732,580
175
1,330
1,459

answered Jun 09 '11 at 22:22

AussieAtHeart

175
7

Did you mean `varchar(36)`? What accounts for the additional two characters when we're dealing with a GUID? – Jeremy Caney Apr 25 '22 at 21:44
1

@JeremyCaney I edited my answer to show a better solution. This was a long time ago, but I think the column in the database had a 50 character length so this is why I had chosen that value. – aarona Apr 26 '22 at 17:03

SQL Server: converting UniqueIdentifier to string in a case statement

4 Answers4

Linked