如何使用mysql对基于整数的varchar id进行排序

Question

Testcase_id in my system are varchar and generated by trigger and I want to sort them on basis of the integer value of the varchar id . I have been so far used three queries but not working at all. I have also tried with the function which converts string to integer. Here I am mentioning that function also and my query well in this query i have not used function.

select t.Testcase_id, 
       CONVERT(SUBSTRING_INDEX('t.Testcase_id','_',2),UNSIGNED INTEGER) as num 
from testcase_master t 
order by num

output(of query):

Answer 1

You have to use -1 instead of 2 inside SUBSTRING_INDEX:

select t.Testcase_id,      
       CONVERT(SUBSTRING_INDEX(t.Testcase_id,'_', -1),UNSIGNED INTEGER) as num 
from testcase_master t 
order by num

According to the manual:

Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned.

Demo here