Have Geek Brain

Sqlite order result

In MySQL my sql looks like this

SELECT * , IF( `Word` = 'sim', 1, IF( `Word` LIKE 'sim%', 2, IF( `Word` LIKE '%sim', 4, 3 ) ) ) AS `sort`
FROM `dblist`
WHERE `Word` LIKE '%sim%'
ORDER BY `sort` , `Word`

This sql doesn't work in SQlite. I want to order the result.

SELECT * FROM dblist where word like 'sim' or word like 'sim%' or word like '%sim%' or word like '%sim'

equal sim is frist, sim% is the second, and% sim% is thrid, and then% sim is the last.

I am currently unable to sort as mysql in sqlite. How to change sql for ordering result?

+2


a source to share


2 answers


You must use a CASE statement instead of an IF , as the latter is not supported by SQLite. Here's the SQL for you:



SELECT
    *,
    CASE WHEN `Word` = 'sim' THEN 1
         WHEN `Word` LIKE 'sim%' THEN 2
         WHEN `Word` LIKE '%sim' THEN 4
         ELSE 3
    END `sort`
FROM `dblist`
WHERE `Word` LIKE '%sim%'
ORDER BY `sort` , `Word`
+3


a source 


SELECT * , case when `Word` = 'sim' then 1 else case when `Word` LIKE 'sim%' then 2 else case when `Word` LIKE '%sim' then 4 else 3 end end end as sort
FROM `dblist`
WHERE `Word` LIKE '%sim%'
ORDER BY `sort` , `Word


+1


a source






More articles:


All Articles