SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Find Upper and Lowercase Value data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

TRACEYSQL
Aged Yak Warrior

586 Posts

Posted - 10/11/2012 :  21:36:31  Show Profile  Send TRACEYSQL an ICQ Message  Reply with Quote
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

USA
319 Posts

Posted - 10/12/2012 :  11:06:51  Show Profile  Reply with Quote
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.


Edited by - lazerath on 10/12/2012 11:07:43
Go to Top of Page

TRACEYSQL
Aged Yak Warrior

586 Posts

Posted - 10/12/2012 :  21:19:40  Show Profile  Send TRACEYSQL an ICQ Message  Reply with Quote
Thank you kindly.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000