Sort 'LIKE' queries result on top and follow by other results-CodePudding

I want the table to sort LIKE queries result first then follow by other result.

Example: data = 'United States', 'New Zealand', 'South Korea'

Query:

SELECT * 
FROM table 
WHERE data LIKE %Korea%;

Output: 'South Korea'

Expected output: 'South Korea', 'United States', 'New Zealand'

I've researched on many places, somehow CHARINDEX its not working, neither is LOCATE (Only shows data 'South Korea'). Database Server - MySQL

CodePudding user response：

The WHERE is restricting what is returned. It looks like you want it all returned but ordered in a certain way. Not tested:

SELECT * FROM table 
    ORDER BY CASE WHEN data LIKE %Korea% THEN 1 ELSE 2

This might also work:

SELECT * FROM table 
    ORDER BY IF(data LIKE %Korea%, 1, 2)

For both, when data contains Korea it is ordered before everything else. You could add , data to then order the remaining data ascending or descending.

CodePudding user response：

As it was already said, it's more SQL than PHP. Then, you want all rows, so WHERE gets in the way.

One way I tested is:

SELECT *, INSTR(data, 'Korea') as weight
FROM table
ORDER BY weight = 0, weight;

This will sort first by the presence of 'Korea' in data (weight is zero if 'Korea' is absent), then by the position of 'Korea' in data.

Using LOCATE is the same as INSTR, but the arguments are reversed:

SELECT data, LOCATE('Korea', data) as weight
FROM table
ORDER BY weight = 0, weight;