Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL SERVER 2000 matching the case

Posted on 2007-08-09
7
Medium Priority
?
165 Views
Last Modified: 2013-11-05
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,
0
Comment
Question by:meperera
7 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 19665431
How odd; that really shouldn't happen.

Please script out the table definition (script "CREATE" table) and post it.  
0
 
LVL 21

Expert Comment

by:ziolko
ID: 19665485
you sure you dont have any trigger?
it doesn't change case when i tried it.

ziolko.
0
 

Author Comment

by:meperera
ID: 19665507
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 11

Expert Comment

by:Swindle
ID: 19665538
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
 
LVL 11

Expert Comment

by:Swindle
ID: 19665567
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
 

Author Comment

by:meperera
ID: 19665702
I can change the PREFERSNAME to UPPER or LOWER without a problem. The only thing I can't  get is mixed case
0
 
LVL 11

Accepted Solution

by:
Swindle earned 2000 total points
ID: 19665919
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question