2

I want to filter a SQLite database in R looking for pattern in string. The problem is the LIKE sql query appear to be case insensitive. Below a reproducible example:

library(DBI)
library(dplyr)

tb <- dplyr::tibble(a=c(rep("aMSq",3), rep("amsq",3), rep("AA",3)))

mydb <- DBI::dbConnect(RSQLite::SQLite(), "")
DBI::dbWriteTable(mydb, "tb", tb, overwrite=T)

### Filtering the table
tb_sqlite <- tbl(mydb, "tb")
tb_sqlite %>% 
  filter( sql("a LIKE '%MS%'"))

dbDisconnect(mydb)

The output is case insensitive:

# Source:   lazy query [?? x 1]
# Database: sqlite 3.22.0 []
  a    
  <chr>
1 aMSq 
2 aMSq 
3 aMSq 
4 amsq 
5 amsq 
6 amsq

How can I make a case sensitive search ? I have seen adding BINARY after the LIKE might do the job but it doesn't.

tks

dauby gilles
  • 57
  • 1
  • 5

1 Answers1

0

In some RDBMS, like PostgreSQL, this is very easy to do, just use ILIKE :D

In SQLite, on first look not that easy, but there is a solution.

You can take a look at this blog for a complete solution(basically, custom function).

If you do not need to support Unicode, just ASCII, then you can use COLLATE NOCASE and your example would look something like:

...a LIKE '%MS%' COLLATE NOCASE
Spasa Mihajlovic
  • 141
  • 1
  • 5