How to sort varchar id on basis integer in it using mysql

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):

Giorgos Betsos · Accepted Answer · 2015-11-24 06:15:21Z

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

edited Nov 24, 2015 at 6:15

answered Nov 24, 2015 at 6:10

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

yogi doshi Over a year ago

thank you so much you have made my day ....if you wish you can approve my question

yogi doshi Over a year ago

not working in my code bro ...it is working from the database side but not working in a my project file please help me .....thanks in advance you can view the query i am facing at drive.google.com/file/d/0B1rpCWtUcfePNFJKQ1dlZFp0bGs/…

Giorgos Betsos Over a year ago

@yogidoshi What do you mean by not working in a my project file? How are you issuing the sql command to the db server?

yogi doshi Over a year ago

there was issue with datatables by default sorting was done so I just removed default sorting now it is working... Thanks bro....

Abhishek Sharma · Accepted Answer · 2015-11-24 06:06:53Z

0

use Cast

CAST(col AS UNSIGNED)

or REPLACE with CAST

CAST(REPLACE(testcase_id,'TC_CTU_','') AS UNSIGNED)

Your query :-

select t.Testcase_id, 
       CAST(REPLACE(testcase_id,'TC_CTU_','') AS UNSIGNED) as num 
from testcase_master t 
order by num

edited Nov 24, 2015 at 6:06

answered Nov 24, 2015 at 6:03

Abhishek Sharma

6,6241 gold badge17 silver badges20 bronze badges

5 Comments

yogi doshi Over a year ago

select t.Testcase_id,CAST(t.Testcase_id as UNSIGNED) as num from testcase_master t order by num... I have tried earlier and also tried now but it result out the same

Abhishek Sharma Over a year ago

CAST(REPLACE(testcase_id,'TC_CTU_','') AS UNSIGNED)

Abhishek Sharma Over a year ago

select t.Testcase_id, CAST(REPLACE(testcase_id,'TC_CTU_','') AS UNSIGNED) as num from testcase_master t order by num

Abhishek Sharma Over a year ago

try this query @yogidoshi

yogi doshi Over a year ago

great this works but every time id changes like TC_CTU and TC_MTU and so on

Collectives™ on Stack Overflow

How to sort varchar id on basis integer in it using mysql

2 Answers 2

4 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related