Custom Room SQL query

Question

I have a query in room DAO, which finds stations based on characters.

@Query("""
    SELECT code, name
    FROM RoomStations 
    WHERE 
    :input IS NULL
    OR 
    LOWER(code) LIKE LOWER(:input)
    OR
    LOWER(name) LIKE LOWER(:input || '%')
    LIMIT 3
""")

it works fine, and returns what I need.

I want it to look, input in code first, if not found then check the name, if found then skip the name.

when input= BE it returns

BAP- BELAPUR,

BAY- BELLARY

WHAT I WANT IT TO DO

BE-BAREILLY

How Can I get this result?

Answer 1

If the interpretation of I want it to look, input in code first, if not found then check the name, if found then skip the name. is correct then the following may be what you want:-

@Query("""
    SELECT code,name 
        FROM roomstations
        WHERE :input IS NULL
        OR code LIKE :input
        OR (name LIKE :input||'%' AND (SELECT count(*) FROM roomstations WHERE code LIKE :input) = 0)
    LIMIT 3
    ;
""")

• LIKE is not case dependant so the lower function has been dropped for brevity, as per:-

  Important Note: SQLite only understands upper/lower case for ASCII characters by default. The LIKE operator is case sensitive by default for unicode characters that are beyond the ASCII range. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE. The ICU extension to SQLite includes an enhanced version of the LIKE operator that does case folding across all unicode characters.

  • https://www.sqlite.org/lang_expr.html#the_like_glob_regexp_match_and_extract_operators

Perhaps consider the following demonstration from an SQLite tool (the suggested way to put together more complex queries):-

DROP TABLE IF EXISTS roomstations; /* just in case make sure the environment is clean */
CREATE TABLE IF NOT EXISTS roomstations (code TEXT,name TEXT); /* create the table for the environment */
/* Addd some testing data */
INSERT INTO roomstations VALUES
    ('BAP','BELAPUR'),
    ('BAY','BELLARY'),
    ('BE','BAREILLY'),
    ('BAP','XXXX'),
    ('BAY','ZZZZ'),
    ('BE','YYYY'),
    ('BAP','WWWW')
;
/* RESULT 1*/
/* Original query from the question */
SELECT code, name
    FROM RoomStations 
    WHERE 
    'BE' /*<<< :input*/ IS NULL
        OR 
        LOWER(code) LIKE LOWER('BE' /*<<< :input*/)
        OR
        LOWER(name) LIKE LOWER('BE' /*<<< :input*/|| '%')
    LIMIT 3
;
/* RESULT 2 */
/* The answer with hardcoded value (would be bound i.e. for room :input instead of the actual value)*/
SELECT code,name 
    FROM roomstations
    WHERE 'BE' /*<<< :input*/ IS NULL
    OR code LIKE 'BE' /*<<< :input*/
    OR (name LIKE 'BE'||'%' /*<<< :input*/ AND (SELECT count(*) FROM roomstations WHERE code LIKE 'BE' /*<<< :input*/) = 0)
LIMIT 3
;
/* RESULT 3 */
/* to cater for testing different values, use a CTE  for the value so it only needs to be changed once */
/* So instead of :input the expression (SELECT * FROM cte_parm) is used */
/* in this case value is BE (so result should match the previous result) */
WITH 
    cte_parm AS (SELECT 'BE')
SELECT code,name
FROM roomstations
WHERE 
    (SELECT * FROM cte_parm) IS NULL
    OR code LIKE (SELECT * FROM cte_parm)
    OR (name LIKE (SELECT * FROM cte_parm)||'%' /*<<< :input*/ AND (SELECT count(*) FROM roomstations WHERE code LIKE (SELECT * FROM cte_parm) /*<<< :input*/) = 0)
LIMIT 3
;
/* RESULT 4 */
/* this time value is BA (there is no BA code and just the single name that starts with BA) */
WITH 
    cte_parm AS (SELECT 'BA')
SELECT code,name
FROM roomstations
WHERE 
    (SELECT * FROM cte_parm) IS NULL
    OR code LIKE (SELECT * FROM cte_parm)
    OR (name LIKE (SELECT * FROM cte_parm)||'%' AND (SELECT count(*) FROM roomstations WHERE code LIKE (SELECT * FROM cte_parm)) = 0)
LIMIT 3
;
/* RESULT 5 */
/* value is null so ALL rows (LIMIT removed) */
WITH 
    cte_parm AS (SELECT null)
SELECT code,name
FROM roomstations
WHERE 
    (SELECT * FROM cte_parm) IS NULL
    OR code LIKE (SELECT * FROM cte_parm)
    OR (name LIKE (SELECT * FROM cte_parm)||'%' AND (SELECT count(*) FROM roomstations WHERE code LIKE (SELECT * FROM cte_parm)) = 0)
/*LIMIT 3 removed to show all */
;
/* Cleanup the test environment */
DROP TABLE IF EXISTS roomstations;

• you should be able to copy and paste and run the above code into any of the SQLite tools (Navicat for SQLite was used)
• data inserted will probably not match as the question does not make it clear exactly what data exists.

The above results in:-

RESULT 1 your query

RESULT2 Just the rows where the code matches BE

RESULT3 same as result 2 but testing the use the the value BE from the CTE

RESULT 4 with the value as BA (node code BA so names that start with BA)

RESULT 5 with NULL as the value i.e. all rows (as LIMIT removed)