SQL Server: converting UniqueIdentifier to string in a case statement

Sql ServerGuidCase

Sql Server Problem Overview


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?

Sql Server Solutions


Solution 1 - Sql Server

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

Solution 2 - Sql Server

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.

Solution 3 - Sql Server

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

CAST(xyz AS char(36))

Solution 4 - Sql Server

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

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionaaronaView Question on Stackoverflow
Solution 1 - Sql ServeraaronaView Answer on Stackoverflow
Solution 2 - Sql ServerPhilippe GrondierView Answer on Stackoverflow
Solution 3 - Sql ServerSilvan HoferView Answer on Stackoverflow
Solution 4 - Sql ServerAussieAtHeartView Answer on Stackoverflow