38393

query with LIKE but case sensitive

Question:

Why isn't my query case sensitive?

Select * from MyTable where name like '%Ann%'

shows record 1 correctly:

Record1= John, Ann, Jack

but shows also record 2:

Record2: Jack, Susanne, Jim

Answer1:

You could execute <a href="http://www.sqlite.org/pragma.html#pragma_case_sensitive_like" rel="nofollow">PRAGMA case_sensitive_like = on</a>, but this would affect all LIKEs used in your program, and disable any index optimizations for prefix searches.

A better idea would be to replace LIKE with <a href="http://www.sqlite.org/lang_expr.html#like" rel="nofollow">GLOB</a>:

SELECT * FROM MyTable WHERE Name GLOB '*Ann*'

Answer2:

[Copied from <a href="https://stackoverflow.com/a/15480401/11654" rel="nofollow">rbedger's answer</a>:]

You can use the UPPER keyword on your case insensitive field then upper-case your like statement

SELECT * FROM mytable WHERE caseSensitiveField like 'test%' AND UPPER(caseInsensitiveField) like 'G2%'

Answer3:

the SQL LIKE is case-insensitive(thnks to CL ) So, on some SQL implementations you <a href="http://blog.sqlauthority.com/2007/04/30/case-sensitive-sql-query-search/" rel="nofollow">can do case Sensitive SQL query Searches</a>

check this too: <a href="https://stackoverflow.com/questions/5391069/case-insensitive-searching-in-oracle" rel="nofollow">Case insensitive searching in Oracle</a>

Recommend

  • youtube api iframe loadvideobyid starts video where last one left off
  • Hidden classes and equivalence between {} object vs. custom constructor (v8)
  • Perl regular expressions in the stringr package
  • ListView list is reversed when selection made
  • Zend DB Select with multiple table joins
  • Javascript, map returns undefined
  • At what point is code reordering in C++ optimization stopped?
  • Compile ffmpeg as stand alone binary
  • IE blocking file download with SSL connections - CacheControl issue
  • Wiring top-level DAGs together
  • django - can't see the uploaded media files from apache
  • MSVC 2015 /Wall has lots of not useful messages
  • Applescript - creating a folder if it doesn't exist
  • Android ContentProvider applyBatch not saving all contact data
  • XMPP push notifications causing problems (delay + duplications) in messages
  • Calculate the depth of subclass in the OWL ontology
  • How to print a java String in a jsp file [duplicate]
  • Interpret strings as packed binary data in C++
  • Best modern way of creating indentation in a ?
  • Swift 3 - Pass struct by reference via UnsafeMutableRawPointer?
  • Retrieve the name of the value a Scala macro invocation will be assigned to
  • C++: Use input string as variable name
  • How to bind Backbone model to jqGrid?
  • jaxb xsd prefix package names
  • iOS, How to check whether the user likes a facebook page or not?
  • How does extglob work with shell parameter expansion?
  • How Does Navigation in xamarin.forms Works?
  • Search files(key) in s3 bucket takes longer time
  • What does Main.1 output file do in XCode?
  • What does the TypeScript “lib” option really do?
  • Javascript/Jquery runs fast in desktop browsers, but slow in mobile/smartphone browsers…should I spl
  • NHibernate manually control fetching
  • Validate child input components on submit with Vee-Validate and vue js 2
  • Intel-64 and ia32 atomic operations acquire-release semantics and GCC 5+
  • Security issues with PHP's Readfile method
  • Differences in dis-assembled C code of GCC and Borland?
  • onBackPressed() not being executed
  • Large data - storage and query
  • Delete MySQLi record without showing the id in the URL
  • Programmatically clearing map cache