Case sensitive search

• Jones'e

  SSC Eights!

  Points: 824

  More actions

  May 3, 2006 at 6:43 am

  #168026

  How can I do a case sensitive search? I only want to return results that match 'DEFAULT' not all that have 'Default'

  WHERE     (fac = 'DEFAULT')

• Gift Peddie

  SSC Guru

  Points: 73570

  More actions

  May 3, 2006 at 7:26 am

  #635589

  Try the link below to get started.  Hope this helps.

  http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm

  Kind regards,

  Gift Peddie

  Kind regards,
  Gift Peddie

• paul_richardson

  SSCarpal Tunnel

  Points: 4594

  More actions

  May 3, 2006 at 7:49 am

  #635601

  hi,

  if your server is case-sensitive then your where clause will only find rows that match "DEFAULT". If your server is case-insensitive then use one of the solutions from the document.

  hope this helps

  Paul

• Riyaz Ghouse

  SSC-Addicted

  Points: 464

  More actions

  May 3, 2006 at 10:45 pm

  #635834

  hey thatz quite simple...

  just follow this way...

  where upper(fac) = upper(watever here)

  or

  where lower(fac) = lower(watever here)

• Riyaz Ghouse

  SSC-Addicted

  Points: 464

  More actions

  May 3, 2006 at 10:45 pm

  #635833

  hey thatz quite simple...

  just follow this way...

  where upper(fac) = upper(watever here)

  or

  where lower(fac) = lower(watever here)

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  May 4, 2006 at 6:34 am

  #635898

  Have you actually tested this solution Riyaz???

• JeffB

  Hall of Fame

  Points: 3541

  More actions

  May 4, 2006 at 10:28 am

  #636021

  Riyaz,

  By doing what you've described, a case INSENSITVE search will result.  The following results in a match even though they are different cases.

  declare @fac varchar(20)

          ,@fac2 varchar(20)

         

  select @fac = 'Abc'

        ,@fac2 = 'aBC'      

  select 'match'

  where UPPER(@fac) = UPPER(@fac2)

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  May 4, 2006 at 10:44 am

  #636033

  Try without the upper.

  See any difference??

• JeffB

  Hall of Fame

  Points: 3541

  More actions

  May 4, 2006 at 10:54 am

  #636038

  I do on a server with a case sensitive collation setting. 

  My point was that no matter what collation is selected, the query will result in a match when using UPPER() or LOWER() and that Riyaz' advice will never work.  In order to do a case sensitive match one of the options previously mentioned should be used if the collation setting is insensitive.

• Riyaz Ghouse

  SSC-Addicted

  Points: 464

  More actions

  May 4, 2006 at 6:15 pm

  #636119

  hey mate...wat i had posted will do a perfect insensitive search...

  but will affect urs...

  sorry should have read ur description clearly...

  i ve now read...but again the solution is simple

  try this...

   where cast(fac as binary) = cast('DEFAULT' as binary)