Can a where statement be case specific?

[bloodbanker]

I want to query for a condition that is case specific.

WHERE
ANTGN_CD_SHT_DESC = 'C Antigen'

Is not the same as

WHERE
ANTGN_CD_SHT_DESC = 'c Antigen'

These are red blood cell antigens.  The two listed above are often referred to as big C or little c.

Can case be built into a query?  The field will always have the word Antigen at the end, and the first part if not always just one letter, for instance, it might be 'Fya Antigen'.  I am accessing our lab system and have no control over that table.

Thank you

[pete_freitag]

I don't think there is a Standard SQL way to do it, but there different ways to do it on different DB servers. What kind of DB is it?

[bloodbanker]

Thank you.

It's coming from MS SQL.

Can it be done with CF?

Below is a snapshot of what a few rows in the database look like.

Here is my query (it's not perfect, but it works and is on an intranet)

<cfquery name="antigen_Lookup" DataSource="xxxxx">
SELECT
*
FROM
[vBWH_PT_antigen_jptm]
WHERE
All_MRN = '#URL.MED_REC_NUM#' 
</cfquery>

[Charlie Arehart · Accepted solution]

There are a couple of ways to solve it. The easiest may be simply to add this phrase after your WHERE clause:

COLLATE Latin1_General_CS_AS

so in your example:

WHERE
All_MRN = '#URL.MED_REC_NUM#' COLLATE Latin1_General_CS_AS

Let us know if that works for you, as a starting point.

Beyond that, you will also see (if you search for info) that you can do things to configure either the entire db or an a given column to always be case-sensitive. That would make sense if your need to do case-sensitive searches is frequent.

But if indeed your need to do it is rather unusual, then this sql-level variation seems the quickest way to get what you want, with no other impact.

(I would add that it may be that there could be a challenge with how SQL Server computes its execution plan for such a query. It may or may not use existing indexes or statistics in the same way it would for a traditional case-insensitive search. If this query you do is significant to your app's functioning, that's something you'll want to dig into, looking at the execution plan generated with and without this clause.)

[bloodbanker]

Thank you.

The source is a view of our lab system.  Our lab system is a commercial product that is FDA cleared and cannot be altered in any way.

Adding COLLATE Latin1_General_CS_AS did the trick!