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.
| Author |
Topic |
|
vaibhavktiwari83
Aged Yak Warrior
843 Posts |
 Posted - 2010-06-19 : 08:03:30
|
| I have a table i want to search rows for columnn case sensetive.means 'vaibhav' will be different from 'VAIBHAV'Vaibhav TTo walk FAST walk ALONE To walk FAR walk TOGETHER |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-19 : 10:30:47
|
| [code]WHERE MyColumn = 'VAIBHAV' COLLATE Latin1_General_BIN2[/code] |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-06-21 : 03:00:01
|
| orWHERE MyColumn = 'VAIBHAV' collate SQL_Latin1_General_CP1_CS_ASMadhivananFailing to plan is Planning to fail |
|
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2010-06-21 : 03:26:27
|
| or casting as varbinary()Where cast ( column_name as varbinary(50))=cast ('VAIBHAV' as varbinary(50))Senthil.C------------------------------------------------------[Microsoft][ODBC SQL Server Driver]Operation canceledhttp://senthilnagore.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-21 : 05:24:12
|
| "collate SQL_Latin1_General_CP1_CS_AS"Isn't that likely to match different characters which happen to collate the same (i.e. if column is using a language-specific collation)?Thus, I think, BINARY collation, rather than case-sensitive + accent-sensitive, is needed?"Where cast ( column_name as varbinary(50))=cast ('VAIBHAV' as varbinary(50))"I think I read somewhere that some COLLATE's can use Indexes (whereas a CAST will have to table-scan). Can't remember the details, but it had to do with Windows collations - either Windows collations FORCE a table scan so use a Non-Windows collation, OR Windows collations CAN be used with an index - I can't remember which way round it was! |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-06-21 : 08:20:48
|
| <<Isn't that likely to match different characters which happen to collate the same (i.e. if column is using a language-specific collation)?>>Not sure about itBut this is the description for SQL_Latin1_General_CP1_CS_ASLatin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 51 on Code Page 1252 for non-Unicode DataMadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-21 : 13:29:44
|
| I'm not sure that there can be two different letters that have the same case, accent but collate the same ... but I don't know how to make a test either!BINARY would fail "width-sensitive" (which your collate would allow) - can't be worrying about that!I think there is an issue that some punctuation in Windows Collation is treated different to same character when using SQL collation - so if comparing Windows Collation column with SQL Collation column but using SQL_Latin1_General_CP1_CS_AS what happens? are the two characters different because of underlying position in collation?I don't know the answer! but BINARY collation would consider those the same.The link you often post (about to Dynamic SQL - but which I can't spell!!) has some information about which Collation is fastest to use for case-insensitive comparison. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-06-22 : 03:23:48
|
<<The link you often post (about to Dynamic SQL - but which I can't spell!!) has some information about which Collation is fastest to use for case-insensitive comparison.>>It is from Sommarskog's siteHe has put lot of informations in his site. Also his site itself a case sensitive one  MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
How search column name case sensetive