SQLite case sensitive

tonelm54
tonelm54 used Ask the Experts™
on
Ive been trying to make a query case sensitive in SQLite but cannot get it to tell the difference between "rv" and "Rv". For example:-
Select * from cars where `design` = 'rv';

Ive searched google and found loads of articles about making it insensitive so "rv" and "Rv" will return the same recordset, but dont know how to do it in reverse.

I did think of trying to change the values into either binary or hex (as obviously R and r will return different values) however cannot find any built in functions to do this.

I have also throught about returning the recordset, then using PHP to do the case comparison, but this seems a but overkill.

Any suggestions / ideas?

Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
I found this https://www.sqlite.org/pragma.html#pragma_case_sensitive_like but I don't understand how to use it.

Commented:
If you installed SQL Server with the default collation options, you might find that the following queries return the same results:
CREATE TABLE mytable 
( 
    mycolumn VARCHAR(10) 
) 
GO 

SET NOCOUNT ON 

INSERT mytable VALUES('Case') 
GO 

SELECT mycolumn FROM mytable WHERE mycolumn='Case' 
SELECT mycolumn FROM mytable WHERE mycolumn='caSE' 
SELECT mycolumn FROM mytable WHERE mycolumn='case'

Open in new window

You can alter your query by forcing collation at the column level:
SELECT myColumn FROM myTable  
    WHERE myColumn COLLATE Latin1_General_CS_AS = 'caSE' 

SELECT myColumn FROM myTable  
    WHERE myColumn COLLATE Latin1_General_CS_AS = 'case' 

SELECT myColumn FROM myTable  
    WHERE myColumn COLLATE Latin1_General_CS_AS = 'Case' 

-- if myColumn has an index, you will likely benefit by adding 
-- AND myColumn = 'case' 

SELECT DATABASEPROPERTYEX('<database name>', 'Collation')

Open in new window

As changing this setting can impact applications and SQL queries, I would isolate this test first. From SQL Server 2000, you can easily run an ALTER TABLE statement to change the sort order of a specific column, forcing it to be case sensitive. First, execute the following query to determine what you need to change it back to:
EXEC sp_help 'mytable'

Open in new window

The second recordset should contain the following information, in a default scenario:

Column_Name Collation

mycolumn SQL_Latin1_General_CP1_CI_AS

Whatever the 'Collation' column returns, you now know what you need to change it back to after you make the following change, which will force case sensitivity:
ALTER TABLE mytable 
    ALTER COLUMN mycolumn VARCHAR(10) 
    COLLATE Latin1_General_CS_AS 
GO 



SELECT mycolumn FROM mytable WHERE mycolumn='Case' 
SELECT mycolumn FROM mytable WHERE mycolumn='caSE' 
SELECT mycolumn FROM mytable WHERE mycolumn='case'

Open in new window

If this screws things up, you can change it back, simply by issuing a new ALTER TABLE statement (be sure to replace my COLLATE identifier with the one you found previously):
ALTER TABLE mytable 
    ALTER COLUMN mycolumn VARCHAR(10) 
    COLLATE SQL_Latin1_General_CP1_CI_AS

Open in new window

If you are stuck with SQL Server 7.0, you can try this workaround, which might be a little more of a performance hit (you should only get a result for the FIRST match):
SELECT mycolumn FROM mytable WHERE 
    mycolumn = 'case' AND 
    CAST(mycolumn AS VARBINARY(10)) = CAST('Case' AS VARBINARY(10)) 

SELECT mycolumn FROM mytable WHERE 
    mycolumn = 'case' AND 
    CAST(mycolumn AS VARBINARY(10)) = CAST('caSE' AS VARBINARY(10)) 

SELECT mycolumn FROM mytable WHERE 
    mycolumn = 'case' AND 
    CAST(mycolumn AS VARBINARY(10)) = CAST('case' AS VARBINARY(10)) 

Open in new window


source:  http://stackoverflow.com/questions/1411161/sql-server-check-case-sensitivity
and worked in my test enviroment.
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
Kulboy, SQLite and SQL Server are two completely different database programs.

Commented:
Sorry , didn't read the question properly.

What i found as an addition to the post of Dave Baldwin: http://stackoverflow.com/questions/15480319/case-sensitive-and-insensitive-like-in-sqlite  but i olso dont get it.
Technology Scientist
Commented:
SQLite is inherently case-insensitive per their protocol; however, a work-around, using a LIKE clause modified for case-sensitively is possible in this case:

First run a PRAGMA query to tell SQLite to change the LIKE behavior from case-insensitive (default) to case-sensitive:

PRAGMA case_sensitive_like = 1;

Open in new window


Then modify your query for an EXACT LIKE query (no wildcards).

SELECT * FROM `cars` WHERE `design` LIKE 'rv';

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial