CODEWITHSUNDEEP
phpsqlitepdocase-sensitive
JBurace
JBurace

Reputation: 5633

SQLite: execute case-sensitive LIKE query on a specific query

I want to run a SELECT ... LIKE query in SQLite that is case-sensitive. But I only want this one query to be case sensitive, and nothing else.

I know there is

PRAGMA case_sensitive_like = boolean;

But that seems to change all LIKE queries.

How can I enable case-sensitive LIKE on a single query?

Examples: I want a query for "FuN" to match "blah FuN blah", but not "foo fun bar".

(This is running under PHP using PDO)

I might be able to toggle that on, then off after the query but I can concerned about the repercussions that may have (efficiency etc). Is there any harm?

I don't have write access to the database.

(This is under Windows Server 2008)

I also tried SELECT id, summary, status FROM Tickets WHERE summary COLLATE BINARY LIKE '%OPS%'; but that did not do a case-sensitive SELECT, it still returned results returns like laptops.

Upvotes: 5

Views: 7855

Answers (6)

gdm
gdm

Reputation: 7938

SELECT * FROM table WHERe field LIKE '%search_term%'

In this form the SELECT is case insensitive.

Upvotes: -2

esertbas
esertbas

Reputation: 486

Try:

 SELECT id, summary, status FROM Tickets WHERE summary GLOB \"*OPS*\";

there is no space between *and OPS.

Upvotes: 3

Alexey Gerasimov
Alexey Gerasimov

Reputation: 2141

You can try something like this:

SELECT YOUR_COLUMN FROM YOUR_TABLE WHERE YOUR_COLUMN COLLATE latin1_general_cs LIKE '%YOUR_VALUE%'

Not sure what your collation set is on the column. I picked latin as an example. Run the query and change 'cs' to 'ci' at the end. You should see different results.

UPDATE

Sorry. read the question too fast. The above collation is for mysql. For SQLLite, you should be able to use BINARY which should give you case sensitive search.

ref: http://www.sqlite.org/datatype3.html#collation

Upvotes: 0

andig
andig

Reputation: 13878

Why not go the simple way of using

PRAGMA case_sensitive_like = true/false;

before and after each query you want to be case sensitve? But beware- case sensitivity does only work for ASCII characters, not Unicode which makes SQlite not fully UC-compliant at this time.

Alternatively, SQlite allows applications to implement the REGEXP operator which might help according to www.sqlite.org/lang_expr.html.

Upvotes: 7

Toby Allen
Toby Allen

Reputation: 11213

I think you may need to do a seperate check in your php code on returned value to see if they match your case-sensitive data.

$rs = mysql_query("Select * from tbl where myfield like '%$Value%'");


while($row == mysql_fetch_assoc($rs))
{

     if (strpos($row['myfield'],$Value) !== false)
     {
        $Matches[] = $row;
      }


 }

 print_R($Matches);

Upvotes: 1

alganet
alganet

Reputation: 2547

You can do that per column, not per query (which may be your case). For this, use sqlite collations. 

CREATE TABLE user (name VARCHAR(255) COLLATE NOCASE);

All LIKE operations on this column then will be case insensitive.

You also can COLLATE on queries even though the column isn't declared with a specific collation:

SELECT * FROM list WHERE name LIKE '%php%' COLLATE NOCASE

Note that only ASCII chars are case insensitive by collation. "A" == "a", but "Æ" != "æ"

SQLite allows you to declare new collation types with sqlite_create_collation and them implement the collation logic on the PHP side, but PDO doesn't expose this.

Upvotes: -1

Related Questions