Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to make update not change case

Author  Topic 

Hommer
Aged Yak Warrior

808 Posts

Posted - 2015-04-15 : 17:17:01
Hi,

This is on the uniqueidentifier column. Both tables are on the same database and have the same COLLATE Latin1_General_CI_AS.

But my plain update and insert results are all upper case.

Then when I tried to force by using
Insert (cols1, cols2) Values (source.col1, source.col2) COLLATE Latin1_General_CI_AS
and
Update set target.col1=source.col1, target.col2=source.col2 target.col1=source.col1 COLLATE Latin1_General_CI_AS


I got
Msg 447, Level 16, State 0, Line 32
Expression type uniqueidentifier is invalid for COLLATE clause.

So what is the trick here?

Thanks!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-15 : 20:41:33
uniqueidentifiers display as upper case by default. They are stored as 16-byte binary, not characters so case is irrelevant in the table (as is collation). If you want to see the guids as lower case try

select lower(myguidcol)
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2015-04-16 : 09:16:08
Thank you for the reply.

They show upper case by default in my SSMS too, but in Toad, show lower case.

And on their way out, it is a vendor app compares them, so I cannot force the case, and the app treats upper <> lower.

I am back to square one.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-16 : 09:52:23
OK, but how they show in SSMS or TOAD is actually irrelevant. What counts is what your application does with them.

Since a GUID is stored as 16-byte binary, when you retrieve it in an application (including vendor applications), it comes in as a binary value. upper/lower case does not apply to binary values.

Please explain what you mean by "on their way out".
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-17 : 07:11:27
Long shot: Sounds like something is casting/covering the GUID to String (and its the "wrong" case!)
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2015-04-17 : 09:36:58
quote:
Originally posted by gbritton

OK, but how they show in SSMS or TOAD is actually irrelevant. What counts is what your application does with them.

Since a GUID is stored as 16-byte binary, when you retrieve it in an application (including vendor applications), it comes in as a binary value. upper/lower case does not apply to binary values.

Please explain what you mean by "on their way out".



"on their way out"=showing in the vendor app, i.e. not from a select statement so I can add lower or upper as you have suggested.

Thanks!
Go to Top of Page
   

- Advertisement -