SQL Server return all SQL_GUID datatypes in uppercase. But I need them in lowercase by default.

I have to return data query as Json format to use in angular front-end and uppercase Guid must pare with same Guid in lowercase but the result of this parison its false.

Is there a way for SQL Server to return Guids in lowecase without use of built-in SQL lower function ?

SQL Server return all SQL_GUID datatypes in uppercase. But I need them in lowercase by default.

I have to return data query as Json format to use in angular front-end and uppercase Guid must pare with same Guid in lowercase but the result of this parison its false.

Is there a way for SQL Server to return Guids in lowecase without use of built-in SQL lower function ?

• javascript
• sql-server
• guid

Share Improve this question Follow edited Dec 31, 2019 at 9:57 Lajos Arpad 77.6k4141 gold badges117117 silver badges223223 bronze badges asked Dec 31, 2019 at 9:42 Nader AfsharNader Afshar 3911 silver badge33 bronze badges 5

• 1 Not a direct answer, but perhaps consider storing your UUIDs as binary in your SQL Server database. In addition to saving some storage space, you could also just pare that binary directly against binary in the front end. – Tim Biegeleisen Commented Dec 31, 2019 at 9:47
• 4 GUIDs are their own data type in SQL Server, uniqueidentifier, and they will always have uppercase letters, if you try SELECT CONVERT(uniqueidentifier,'f6af441f-bea8-4760-86ab-91464e55b091') it will be in upper case. You will have to treat the value as a varchar and use lower if you want lowercase letters. I don't, suggest storing the values as a varchar; just pass the lowercase value when dealing with your application. – Thom A Commented Dec 31, 2019 at 9:47
• 1 Can you elaborate on why you don't want to use LOWER? – Martin Smith Commented Dec 31, 2019 at 10:40
• 3 My two centavos on this - you're talking about format, not content... and that's not something the database should be concerned about. If it's a GUID, then send it back as a GUID, and let the js figure out what it needs to do with it to make the parison. If it was a Date, we wouldn't be having this discussion... we'd be letting SQL Server send back a date and then using Moment, or a js Date to "format" it to do the parison, not SQL Server.... so why should a GUID/UUID be any different? – TechGnome Commented Dec 31, 2019 at 12:58
• @MartinSmith all of my logic are in sp's and functions and finally results are returns as JSON format and i deliver it to angular front-end , if i want to use lower i have to make a lot of changes , be side i think it's not a clean cod if i write lower before all GUIDs fields in final select querys also in angular . – Nader Afshar Commented Jan 18, 2020 at 5:51

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 3

The GUID is basically a 16-byte binary value. There are no items like upper case or lower case in binary values (or integers).You can use ToUpper or ToLower to make them look the way you want.

to upper case in SQL Server

SELECT UPPER ('8B94A1DA-1956-4D2E-A3A3-30CC52F589F9');

to lower case in SQL Server

SELECT LOWER ('8B94A1DA-1956-4D2E-A3A3-30CC52F589F9');

to upper case in angular

'8B94A1DA-1956-4D2E-A3A3-30CC52F589F9'.toUpperCase( );

to lower case in angular

'8B94A1DA-1956-4D2E-A3A3-30CC52F589F9'.toLowerCase( );

Workaround: If you would like to store GUIDs formatted as lowercase string in your SQL DB table, you can add a puted column, or write a view that returns the GUID as lower. This sample implicitly converts the GUID to VARCHAR and formats in lowercase.

declare @t table(
    u uniqueidentifier, 
    GuidAsLower as lower(u)
)
insert @t values(newid())
select * from @t

Returns

u:F0BA5160-F5DE-779D-B336-2012B09DDB7F  
GuidAsLower:f0ba5160-f5de-779d-b336-2012b09ddb7f