Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Find Upper and Lowercase Value data

Author  Topic 

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2012-10-11 : 21:36:31
I have a table for example PEOPLE with PEOPLE_ID this table has index on it that is just a unique key

The table is not set to collation of SQL_Latin1_General_CP1_CI_AS but we are converting it to SQL_Latin1_General_CP850_BIN

Prior to this we may have CA01 and ca01

I cannot seem to find out how I can read the entire database to find this value to fix the data. Is there a way to pull out all the records that are the same but the case is difference rather than searching entire table.

Cheers

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2012-10-12 : 11:06:51
Try this:

DECLARE @MyTable table (MyNameColumn varchar(100) collate SQL_Latin1_General_CP1_CI_AS);

INSERT @MyTable values ('bill');
INSERT @MyTable values ('Bill');
INSERT @MyTable values ('BILL');
INSERT @MyTable values ('BILL');
INSERT @MyTable values ('Sue');
INSERT @MyTable values ('Sue');
INSERT @MyTable values ('John');
INSERT @MyTable values ('Dan');
INSERT @MyTable values ('DAN');
INSERT @MyTable values ('Ralph');


WITH CTE1
AS
(
SELECT MyNameColumn, COUNT(*) OVER(PARTITION BY MyNameColumn) AS Cnt
FROM @MyTable
GROUP BY MyNameColumn, CONVERT(VARBINARY(100),MyNameColumn)
)
SELECT MyNameColumn
FROM CTE1
WHERE cnt > 1

/* -- output
MyNameColumn
BILL
Bill
bill
DAN
Dan
*/




It gets a distinct list of names that have different cases within the dataset. You can expand this concept as needed.

Go to Top of Page

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2012-10-12 : 21:19:40
Thank you kindly.
Go to Top of Page
   

- Advertisement -