SQLite case sensitive

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
tonelm54Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
I found this https://www.sqlite.org/pragma.html#pragma_case_sensitive_like but I don't understand how to use it.
0
kulboyCommented:
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.
0
Dave BaldwinFixer of ProblemsCommented:
Kulboy, SQLite and SQL Server are two completely different database programs.
0
kulboyCommented:
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.
0
NerdsOfTechTechnology ScientistCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.