This post is referring from my previous post regarding Sort MySQL Varchar Column

The issue is the IC prefix is not only G, there are new prefix with RF.

So when I’m sorting with ORDER BY SUBSTR(IC,2)+0 ASC

It will read from second letter of G12345 that is no 1

But if the prefix is RF12345 so it will read at F so the sort will not work.

So now i need to order by case,  If IC with prefix G the substr should use ORDER BY SUBSTR(IC,2)+0 ASC

And IC prefix with RF the substr should use ORDER BY SUBSTR(IC,3)+0 ASC

I use this statement to solve the problem:

ORDER by
    CASE
         when ic like ‘RF%’ THEN SUBSTR(nobadan,3)+0
         when ic like ‘G%’ THEN SUBSTR(nobadan,2)+0
         END ASC