2

am trying to perform a case sensitive serach of all columns in a table, so i did somthing like this

Select * From mytable Where col1 || '--' || col2 || '--' || etc like '%SomeValue%'

but it alwas return the same result for both upper case and lower case. if i do this

Select * From mytable Where col1 like '%SomeValue%' OR col1 like '%SomeValue%' etc

i get the desired result. The problems here is that i cannot use this second query as i have about 36 columns to search, and writing col1 like '%SomeValue%' up to 36 times would be unecessary.

does anyone have any solution?

Improve this question
4
  • You might want to consider sqlite fulltext, breaking text into a words and creating a "long schema" index or words keyed back to identities or a combination. Seems like scanning 36 columns is the wrong approach ...
    – bryanmac
    Commented Oct 4, 2012 at 23:28
  • i already went that path, but it makes all other query slow, i got this solution from here stackoverflow.com/questions/2428495/…
    – Smith
    Commented Oct 4, 2012 at 23:46
  • Not necessarily the main path for other queries - more like a look up table off to the side for these searches. That table for searching could also use a fulltext compiled version of sqlite if you want it to be very fast.
    – bryanmac
    Commented Oct 5, 2012 at 0:07
  • The downside is the extra time to word break/index or you have to make it async/background if some latency on the index is acceptable.
    – bryanmac
    Commented Oct 5, 2012 at 0:08

1 Answer 1

Reset to default
2

One solution is to use glob instead of like

sqlite> select * from sqlite_master where ('foo' || '--' || 'bar') like '%Bar%';
table|t|t|2|CREATE TABLE t (a)
sqlite> select * from sqlite_master where ('foo' || '--' || 'bar') glob '*Bar*';
sqlite> select * from sqlite_master where ('foo' || '--' || 'bar') glob '*bar*';
table|t|t|2|CREATE TABLE t (a)
sqlite>

Another solution is to use pragma case_sensitive_like

sqlite> PRAGMA case_sensitive_like = 1;
sqlite> select * from sqlite_master where ('foo' || '--' || 'bar') like '%Bar%';
sqlite> select * from sqlite_master where ('foo' || '--' || 'bar') like '%bar%';
table|t|t|2|CREATE TABLE t (a)
sqlite>
Improve this answer
0

Not the answer you're looking for? Browse other questions tagged or ask your own question.