Case Insensitivity

[bjorgenson]

I have a SQL 2000 database. I have a ASP.NET web app that I use to
search this database. I need to make my data case insensitive,
espcially my last name column. How do I change this?

Thanks,
Brian

[bjorgenson]

I was doing some further reading and I am hearing that you set case
sensitivity when you first install SQL by choosing an ANSI set and the
only way to change this is to re-install SQL. Is this correct? There
has to be another way around this....

[Simon Hayes]

See "Specifying Collations" and "Collation Precedence" in Books
Online. You can change the collation at the database or column level
(see ALTER DATABASE and ALTER TABLE), or in your queries (see COLLATE).
Personally, I would modify the queries (or perhaps create a view)
rather than have one or two columns in a database in a different
collation from the rest.

Simon

[David Portas]

There is another way in SQL2000. Collation is determined at column
level so you can alter the case-sensitivity and other collation
properties at any time. For example:

ALTER TABLE YourTable
ALTER COLUMN last_name VARCHAR(50)
COLLATE Latin1_General_ CI_AS

Read the Collations topics in Books Online to understand the collation
syntax and how this affects comparisons between columns of different
collation.

--
David Portas
SQL Server MVP
--

[bjorgenson]

I used your syntax and everything works like a charm except for one
thing, now when I do a search, such as "W" in the lastname field, it
pulls every records that contains a "W" in the last name, rather than
names that start with "W". How do you correct this? it needs to search
from left to right.

Thanks,
Brian

[Stu]

What's the SQL statement you are using to SELECT? It sounds like
you're putting a wildcard in front of and behind the character you are
searching on, e.g.:

SELECT ColName
FROM Table
WHERE ColName LIKE '%W%'

when it sounds like you want the wildcard after

SELECT ColName
FROM Table
WHERE ColName LIKE 'W%'

Your collation settings should only affect the case sensity of the
database; not how your LIKE comparisons perform. Am I
misunderstandin g?

Stu