| Author |
Topic |
|
shree_uday
Starting Member
2 Posts |
 Posted - 2010-05-10 : 12:39:16
|
| Hi to all sql gurus, I want to display the all the reocrds without considering the case. for an eg.if there is record have the value called "test", if i give the value as "test", or "TEST" or TEst or teST and etc...it will display the all record have the value "test". kindly help me out. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-05-10 : 12:58:45
|
| just apply a case insensitive collation like SQL_Latin1_General_CP1_CI_AS------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-10 : 13:01:00
|
| I would apply the same collation as currently used but substitute CS (Case Sensitive) for CI (Case Insensitive) - that should retain the accent / language collation aspects. |
|
|
|
shree_uday
Starting Member
2 Posts |
Posted - 2010-05-10 : 13:15:14
|
| thanks for the reply.. actually im working in sql navigator...i want to excute the table with all possiblities of case value...suppose if the record having the value called test. when i give the values like teST or TEST or TEst or test when i execute... it will display all the records having the value called "test". |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-05-10 : 13:20:07
|
quote:
Originally posted by shree_uday
thanks for the reply.. actually im working in sql navigator...i want to excute the table with all possiblities of case value...suppose if the record having the value called test. when i give the values like teST or TEST or TEst or test when i execute... it will display all the records having the value called "test".
then wat i suggested is enough.just do likeWHERE yourcol COLLATE <case sensitive collation> = 'test'------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-10 : 13:22:08
|
| Have you checked what Collation the column uses as requested above?As stated above, IF the field is defined with a case INSensitive collation then comparing the record with "Test" "TEST" or "test" will all be TRUE.IF the collation defined on the field is Case SENSITIVE then the comparison will only be true if the correct case is used. In that case you need to force the collation (to use a Case INSensitive collation just for your comparison test) as described above |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-10 : 13:24:16
|
| @Visakh: Typo? "WHERE yourcol COLLATE <case INsensitive collation> = 'test'" ? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-05-10 : 13:26:28
|
quote:
Originally posted by Kristen
@Visakh: Typo? "WHERE yourcol COLLATE <case INsensitive collation> = 'test'" ?
oops yeah nice catch------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
case insensitive