CODEWITHSUNDEEP
mysqlsql-order-by
Mint
Mint

Reputation: 15957

How to ORDER MySQL results while excluding a string from the order title

Im trying use MySQL to order by excluding "excl" if it's found at the start of a string, and using what ever comes after "excl" to order the results.

So it will order like this:

title a name
excl title b name2
title c name
title d name

But not:

title a name
title c name
title d name
excl title b name2

Upvotes: 0

Views: 253

Answers (2)

user330315
user330315

Reputation:

Edit: misread the question, I thought rows starting with excl should go to the end, so the CASE needs to use substring() like in enobrev's answer.

...
ORDER BY 
   CASE 
      WHEN title_column LIKE 'excl%' THEN substring(title, 6)
      ELSE title_column
   END

Upvotes: 2

enobrev
enobrev

Reputation: 22542

You want to tell the ORDER clause that if the string begins with the text you're looking for, then use a substring that comes after that string. So "excl title a" becomes "title a" as far as the ORDER process is concerned.

SELECT  title
FROM    table_name
ORDER BY
IF (SUBSTRING(LOWER(title), 1, 5) = 'excl ',
    SUBSTRING(title, 6),
    title
)

Upvotes: 1

Related Questions