Skip to main content
B
bloodbanker
Known Participant
May 17, 2021
Answered

Can a where statement be case specific?

  • May 17, 2021
  • 1 reply
  • 546 views

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

    This topic has been closed for replies.
    Correct answer Charlie Arehart

    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.)

    1 reply

    pete_freitag
    pete_freitag
    Participating Frequently
    May 17, 2021

    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?

    B
    bloodbankerAuthor
    Known Participant
    May 17, 2021

    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
    Community Expert
    Charlie ArehartCommunity ExpertCorrect answer
    Community Expert
    May 17, 2021

    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.)

    /Charlie (troubleshooter, carehart. org)
      Terms & ConditionsAccessibility statement
      Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices