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 |
|
Blastrix
Posting Yak Master
208 Posts |
 Posted - 2004-11-26 : 13:51:36
|
| Is there any way to perform a case sensitive LIKE comparison on the case insenstive DB? I tried converting to varbinary() like I do for equality comparisons, but it turns out that the behavior of the LIKE operator converts the comparison values to a string type thus disabling the case sensitive ability.Thanks,Steve |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-26 : 23:29:16
|
| Force the COLLATE (in the LIKE clause) to a Binary collation sequence?Kristen |
 |
|
|
Blastrix
Posting Yak Master
208 Posts |
Posted - 2004-11-29 : 13:03:40
|
| This gets me close, but not quite there. I need to be able to collate for case sensitivity on the fly. I tried doing this with a CASE statement, but I can't seem to get it to work with COLLATE. I tried both:val1 LIKE val2 COLLATE CASE WHEN IsCaseSensitive = 1 THEN SQL_Latin1_General_CP1_CS_AS ELSE SQL_Latin1_General_CP1_CI_AS ENDandval1 LIKE val2 CASE WHEN IsCaseSensitive = 1 THEN COLLATE SQL_Latin1_General_CP1_CS_AS ELSECOLLATE SQL_Latin1_General_CP1_CI_AS ENDAny thoughts on how I could accomplish this on the fly?Thanks,Steve |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-11-29 : 13:06:32
|
| How about using the UPPER or LOWER functions ?rockmoose |
|
|
|
Blastrix
Posting Yak Master
208 Posts |
Posted - 2004-11-29 : 13:15:43
|
| Excellent, thanks for the idea, it did the trick. So the solution I used was to COLLATE case-sensitive, and then apply the CASE statements to the values being compared. If case-insensitive, then I converted the values using UPPER(). Works great. Thanks guys. |
|
|
|
|
|
|
Case-sensitive LIKE on case-insensitive DB?