CODEWITHSUNDEEP
phpsqlite
Micha
Micha

Reputation: 383

SQLite3 CASE and LIKE statement difficulties

I have a statement in sqlite3 that is giving me a bit of trouble. I in part understand why its not working I just need to figure out a solution. I have a columns containing titles. Some titles have 'The' as a prefix. I want to skip over 'The' and grab the next relevant character. Basically I want to grab all results with the letter A. For instance "The Abyss". I want to ignore 'The' and the space and see if the next letter is relevant to the search. However not all the titles are prefixed with a 'The' Here is my sqlite3 statement. I understand that it isn't working because I am only searching by the first character in the column making it impossible to see if a 'The' even exists. How can I make this work?

SELECT *,CASE WHEN substr(MovieTitle,1,4) = 'The ' THEN
substr(MovieTitle,5) ELSE MovieTitle END FROM Movies WHERE MovieTitle LIKE 'a%'

Upvotes: 1

Views: 63

Answers (1)

Vinay
Vinay

Reputation: 7676

So if I search for 'a' it should bring up 'The Abyss' , with 'c' it should bring up 'The Croods'. You can combine CASE statement with WHERE to achieve the results .Also note the lower() function I added in order to catch all variations of the word 'The ' - 'the ' 'The ' or 'THE '

SELECT * from movies   WHERE
( 
    CASE WHEN lower( substr( MovieTitle,1,4) ) = 'the ' 
    THEN  
    substr( MovieTitle,5) 
    ELSE  MovieTitle END COLLATE NOCASE 
) like 'C%'  

COLLATE NOCASE


`COLLATE NOCASE` Above will catch all variations of the next word like 'Croods' - 'croods' or ' CroodS'

PHP

$sql = "SELECT * from movies   WHERE
        ( 
            CASE WHEN lower( substr( MovieTitle,1,4) ) = 'the ' 
            THEN  
            substr( MovieTitle,5) 
            ELSE  MovieTitle END COLLATE NOCASE 
        )   like :searchText  

        COLLATE NOCASE";


$db = new SQLite3('mydb.db');

$stmt = $db->prepare($sql);
$stmt->bindValue(':searchText', $keyword . '%' , SQLITE3_TEXT); //$keyword is user typed
$result = $stmt->execute();
var_dump($result->fetchArray());

Upvotes: 3

Related Questions