How do I do a case sensitive comparison in sql?

Question

Just started a tutorial in SQL for beginners. I'm doing some exercises now and I would like to know how to change the title.

If you look at here: you'll see that I have made firstname, lastname, title, age and salary. And I wrote the letters in small letter. How can I change it to capital letter?

http://tinypic.com/r/amtpgm/3

I tried using this:

update mytablename 
set firstname = 'Firstname'
where firstname = 'firstname'

But I later realized that this one will not work.

Thanks

====

additional question:

I also notice that if I wrote with spaces, then its not recognized. It's the first part only which will be displayed. Do you know why is it doing? Thanks

create table myemployees_tr0214
(First Name varchar(20),
Last Name varchar(20),
Title char(5),
Age number(3),
Salary number(6,10));

==========

thank you for all your inputs. I've tried this one in renaming the "Firstname" to "Fname" and it didn't work. Did I miss something?

alter table myemployees_tr0214
rename column Firstname to Fname;

Answer 1

This may need to be optimize for more but it will allow you to even update mulitple first name MSSQL version

select 'mary ellen' as firstname into #test
insert into #test select 'Nathan'


select 
Case when patindex('% %',firstname) >0 then
upper(left(firstname,1)) --first letter
+ rtrim(substring(firstname,2,patindex('% %',firstname)-1)) --whole firstname
+ ' ' -- space
+ Upper(substring(firstname,patindex('% %',firstname)+1,1)) --first letter last name
+  rtrim(substring(firstname,patindex('% %',firstname)+2, len(firstname)))
else
upper(left(firstname,1)) + substring(firstname,2,len(firstname))
end as firstname
from #test


update #test
set firstname = Case when patindex('% %',firstname) >0 then
upper(left(firstname,1)) --first letter
+ rtrim(substring(firstname,2,patindex('% %',firstname)-1)) --whole firstname
+ ' ' -- space
+ Upper(substring(firstname,patindex('% %',firstname)+1,1)) --first letter last name
+  rtrim(substring(firstname,patindex('% %',firstname)+2, len(firstname)))
else
upper(left(firstname,1)) + substring(firstname,2,len(firstname))
end 

Answer 2

Ah, there are two ways to read this question. The first is based on reading the sample UPDATE you posted. This will fail because SQL by default doesn't do a case sensitive comparison on strings.

The second piece of code implies what you wanted was to ALTER TABLE and change the name of the column from a column name with one casing to another. In MS-SQL, you can't do that without dropping the whole table and re-creating it, in other dialects of SQL there will be version specific DDL syntax.

And finally, in MS-Access, if a column name has a space, you wrap it in double quotes, e.g. "My Column" in SQL wrap it in [My Column]

Answer 3

If you're using mysql see: http://dev.mysql.com/doc/refman/5.0/en/charset-binary-op.html

The BINARY operator casts the string following it to a binary string. This is an easy way to force a comparison to be done byte by byte rather than character by character. BINARY also causes trailing spaces to be significant

mysql> SELECT 'a' = 'A';
        -> 1
mysql> SELECT BINARY 'a' = 'A';
        -> 0
mysql> SELECT 'a' = 'a ';
        -> 1
mysql> SELECT BINARY 'a' = 'a ';
        -> 0

Answer 4

For MS SQL Server...

You'd use [ and ] to delimit identifiers:

create table myemployees_tr0214 (
  [First Name] varchar(20),  --here
  [Last Name] varchar(20),  --here
  Title char(5),
  Age number(3),
  Salary number(6,10)
);

If you want to change the column name from "firstname" to "Firstname", you could use sp_rename in MS SQL Server.

If you want to change the first letter of the data in the "firstname" column, other posters have offered solutions and here's another for a single name.

update mytablename 
set firstname = 'Firstname'
where firstname COLLATE Latin1_general_Bin = 'firstname' COLLATE Latin1_general_Bin

Answer 5

First, unless you really want to change the names of the fields, don't. It's not really all that important if all you're doing is learning SQL. Note that if you want to learn the syntax for doing so, then of course it would be a worthwhile exercise, but other than that, I'd let it be.

Your edited question mentions using spaces in names. This is not allowed. The rules for what constitutes a "SQL Identifier", be it the name of a table, column, constraint, etc. has some strict rules, and simplified they are that you should only use letters, underscores, and digits, except that you can't start with a digit.

Now, why the online website that you're using to learn SQL through doesn't complain when you add those spaces, that I don't know, and to me that makes it a little suspect. It doesn't sound as though it actually uses a known database engine, as just the presence of those spaces there + the extra words would make any normal database engine complain about bad syntax.

In order to fix it, either add underscores instead of spaces, or contract and use camelCasing, like this: FirstName, LastName

Answer 6

This should update all the firstnames in the table to an uppercase first letter:

UPDATE mytablename SET firstname = CONCAT(UCASE(MID(firstname,1,1)),MID(firstname,2));

Hope this helps you :)

Answer 7

update mytablename set firstname = 'Firstname'; where firstname = 'firstname';

This will update the values of the firstname column. What you are trying to do is change the name of the firstname column. How to do this depends on the database you're using (which you haven't mentioned).

If it's MS Access or SQL server, you can open the table in the UI and use the designer to change the column name.

Otherwise you can use the SQL ALTER TABLE statement, as described here.

Answer 8

you'll need some kind of unique row indentifier like id so you can do

update mytablename set firstname = 'Firstname' where id = 1

now what can be used as a unique row indentifier is a huge debate of natural vs surrogate keys. use what you think is best for your example but i'm a supporter of surogate keys since every natural key has the possibility to change.