Case in-sensitiveness
By default, a query involving a
like does not take into effect the case of the strings compared. This cannot even be changed with the
collate expression.
In order to force case sensitiveness in queries with a
like expression, the
pragma case_sensitive_like must be turned on:
create table tab (
col text
);
insert into tab values ('abc def ghi.');
insert into tab values ('Abc Def Ghi.');
insert into tab values ('ABC DEF GHI.');
select * from tab where col like '%Def%';
--
-- abc def ghi.
-- Abc Def Ghi.
-- ABC DEF GHI.
select * from tab where col like '%Def%' collate binary;
--
-- abc def ghi.
-- Abc Def Ghi.
-- ABC DEF GHI.
select * from tab where col collate binary like '%Def%' collate binary;
--
-- abc def ghi.
-- Abc Def Ghi.
-- ABC DEF GHI.
pragma case_sensitive_like = true;
select * from tab where col like '%Def%';
--
-- Abc Def Ghi.