Case Sensitive in Select Statement

1

Hi,

I have the following within my SELECT statement;

ISNULL(CASE WHEN LEFT(EMISJun15Btch1.dbo.Problem.ReadCode, 5) = 'PCSDT' THEN 'PCSDT' WHEN LEFT(EMISJun15Btch1.dbo.Problem.ReadCode, 7) 
                      = 'EMISATT' THEN 'EMISATT' ELSE EMISJun15Btch1.dbo.Problem.ReadCode END, N'(novalue)') COLLATE SQL_Latin1_General_CP1_CS_AS = ISNULL(dbo.CPRDLkupMedical.Readcode,
                       N'(novalue)') COLLATE SQL_Latin1_General_CP1_CS_AS

N/B: EMISJun15Btch1.dbo.Problem.ReadCode field the Collation SQL_Latin1_General_CP1_CS_AS
dbo.CPRDLkupMedical.Readcode field the Collation SQL_Latin1_General_CP1_CS_AS

The issue is in EMISJun15Btch1.dbo.Problem.ReadCode

I have the following codes;

ReadCode
13LQ
13lQ

and dbo.CPRDLkupMedical.Readcode (only one record)

ReadCode
13lQ

So, it pulls all records with ONLY 13IQ and neglect all records with 13LQ.

I want it to compare between the two tables and see 13IQ AND 13LQ as similar record. Therefore, pull all records with readcode 13LQ too.

Any ideas please.. Thanks

2

If you are trying to do case insensitive comparison, force the collation to SQL_Latin1_General_CP1_CI_AS instead of SQL_Latin1_General_CP1_CS_AS as you are doing.

Alternatively, just convert both sides of the comparison to upper (or lower) case using the UPPER or LOWER function.

3

Is that safe for foreign accents etc. (when compared to just using a Case Insensitive collation)? I don't know the answer, just curious!

My instinct is that a Case Insenitive collation would be faster / use an index, if available, but I don't know the answer to that one either!!

4

I don't know for certain either, but my sense is that it should still work if the the left and right sides were the same/compatible collations to start with. The functions UPPER and LOWER are collation aware - i.e., they return results in the same collation as that of the input. So if OP was using the COLLATE to force two different collations to be nice to each other, then UPPER and LOWER wouldn't fix the problem.

1 Like