SQL SERVER 2000 matching the case

Hi Experts,
  I have a SQL table(CustNames) that has the following fields.
FirstName
LastName
Address
PrefersName
etc..,

I want to make the PrefersName field the same as the Firstname.I have tried the follwing SQL command.
UPDATE  CUSTNAMES SET PREFERSNAME=FIRSTNAME ;

However my problem is that it changes everything from mixed case ti uper case.
eg.., John becomes JOHN

How do I get around this ?

Cheers,
mepereraAsked:
Who is Participating?
 
SwindleCommented:
I know this doesn't solve the mystery of why it isn't preserving the case when you copied it over, but you could do this to get it fixed now.  Since it is a firstname field, it should be rare that you have a person with multiple capital letters in it (like you would on a lastname field).

UPDATE Custnames SET Prefersname = UPPER(substring(prefersname,1, 1)) + LOWER(substring(prefersname,2,len(prefersname) - 1))
0
 
Scott PletcherSenior DBACommented:
How odd; that really shouldn't happen.

Please script out the table definition (script "CREATE" table) and post it.  
0
 
ziolkoCommented:
you sure you dont have any trigger?
it doesn't change case when i tried it.

ziolko.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mepereraAuthor Commented:
Thanks ScottPletcher:.
  Unfortunately I haven't got access to the create table script as that was wrttem by someone else. I am the maintainence person. Is there a way to copy the binary values and convert them instead of copying the string ?

Thanks again,
0
 
SwindleCommented:
zoilko was going the same route as my mind was going.  It sounds like you have a trigger that is set on the table that is doing an UPPER(value) to the data you are trying to insert there.
0
 
SwindleCommented:
I just realized you may not have permissions to look for the trigger.  If you don't have access to look at the triggers trying doing this:

UPDATE  CUSTNAMES SET PREFERSNAME=LOWER(FIRSTNAME)

If you run that and then check the data again and it's still capitalized I'd be about 99% sure that's what's going on.

0
 
mepereraAuthor Commented:
I can change the PREFERSNAME to UPPER or LOWER without a problem. The only thing I can't  get is mixed case
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.