SELECT DISTINCT
-
Hi,
I have a column that has a mixture of upper case and lower case values. Some values are the same, eg, AGE and Age. When I do a SELECT DISTINCT (column_name), I only get one row for the value, regardless of upper or lower case, eg AGE. I have the same table with the same values in DB2, and the SELECT DISTINCT gives me both values. How do I get both values in SQL Server?
thanks!
-
Well - your database is case insensitive - so you have one of two options...either make the whole database case sensitive or make only the column/s that you are referencing case sensitive...not sure if this is the right collation or not.."SQL_Latin1_General_Cp437_CS_AS" (pl. read up and/or experiment in a test db if you can)...
Here's info from BOL that should help you decide what you have to do in your particular situation....
You can change the collation of a column by using the ALTER TABLE statement:
CREATE TABLE MyTable
(PrimaryKey int PRIMARY KEY,
CharCol varchar(10) COLLATE French_CI_AS NOT NULL
)
GO
ALTER TABLE MyTable ALTER COLUMN CharCol
varchar(10)COLLATE Latin1_General_CI_AS NOT NULL
GO
You cannot alter the collation of a column that is currently referenced by:
A computed column.
An index.
Distribution statistics, either generated automatically or by the CREATE STATISTICS statement.
A CHECK constraint.
A FOREIGN KEY constraint.
You can also use the COLLATE clause on an ALTER DATABASE to change the default collation of the database:
ALTER DATABASE MyDatabase COLLATE French_CI_AS
Altering the default collation of a database does not change the collations of the columns in any existing user-defined tables. These can be changed with ALTER TABLE. The COLLATE CLAUSE on an ALTER DATABASE statement changes:
The default collation for the database. This new default collation is applied to all columns, user-defined data types, variables, and parameters subsequently created in the database. It is also used when resolving the object identifiers specified in SQL statements against the objects defined in the database.
Any char, varchar, text, nchar, nvarchar, or ntext columns in system tables to the new collation.
All existing char, varchar, text, nchar, nvarchar, or ntext parameters and scalar return values for stored procedures and user-defined functions to the new collation.
The char, varchar, text, nchar, nvarchar, or ntext system data types, and all user-defined data types based on these system data types, to the new default collation.
After a collation has been assigned to any object other than a column or database, you cannot change the collation except by dropping and re-creating the object. This can be a complex operation. To change the default collation for an instance of Microsoft® SQL Server™ 2000 you must:
Make sure you have all of the information or scripts needed to re-create your user databases and all of the objects in them.
Export all of your data using a tool such as bulk copy.
Drop all of the user databases.
Rebuild the master database specifying the new collation.
Create all of the databases and all of the objects in them.
Import all of your data.
Note: Instead of changing the default collation of an instance of SQL Server 2000, you can specify a default collation for each new database you create.
**ASCII stupid question, get a stupid ANSI !!!** -
Thanks for your reply.
But I don't actually want to permanently change the collation sequence of the column...is there any way I can convert the data to case sensitive through my SQL Select statement?
(not sure if this really makes sense?)....tks.
-
hmm - I know exactly what you're asking but have no idea if this can be done with a sql select - I keep thinking it would help using ascii values - just not sure how...
oh - I was doing a search on this site and hit upon this link - maybe you can use DSP's update solution and try playing around with it ?!
Other Post
Sorry couldn't be of more help!
**ASCII stupid question, get a stupid ANSI !!!** -
Thanks again. You put me on the right track!
For those that are interested, I found the answer:
SELECT DISTINCT(column_name) COLLATE (collation_name)
FROM .....
-
Use the COLLATE keyword. See this example (you'll have to change the collation for whichever you use):
CREATE TABLE FOO (bar varchar(10) COLLATE Finnish_Swedish_CI_AS)go
INSERT INTO FOO VALUES ('AGE')
INSERT INTO FOO VALUES ('age')
INSERT INTO FOO VALUES ('Age')
SELECT DISTINCT bar FROM foo
SELECT DISTINCT bar COLLATE Finnish_Swedish_CS_AS FROM FOO
This code creates a table FOO with a varchar type column bar, with collation specified to Finnish_Swedish and CI (case insensitive). The first query therefore returns only one row, but the second query returns all three rows because the column is treated case sensitively.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply