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:
when ic like ‘RF%’ THEN SUBSTR(nobadan,3)+0
when ic like ‘G%’ THEN SUBSTR(nobadan,2)+0