SQL Query, Sort on Substring

Question

I wanted to sort results returned from SQL query on the basis of Substring. I am using MYSQL as DB. I am trying to achieve two different results

Sample data
aaa ad aa
aaa ab
aaa ac

1) First i want to sort on the basis of second substring.

Result:
aaa ab
aaa ac
aaa ad aa

2) Second i want to sort on the basis of last substring.

Result:
aaa ad aa
aaa ab
aaa ac

How can i achieve these tasks.

Much appreciate your help.. Regards

Edit 1: Substrings can be of any length. Also for the second case i want sorting on the basis of last substring. Which means it can be on any position..

Are the sub strings fixed length / position or are they delimited? — Kickstart
– Kickstart, Commented Dec 13, 2013 at 12:13
None of the substring is fixed length also in second case i just want to sort on last substring it can be on any position.... — planet260
– planet260, Commented Dec 13, 2013 at 12:14
It seems then that you want to take the string, explode it out into its delimited parts, pick one of them and sort based on that. It is possible but it is not going to be pretty or efficient. — Kickstart
– Kickstart, Commented Dec 13, 2013 at 12:20

Saharsh Shah · Accepted Answer · 2013-12-13 13:19:39Z

2

Try this:

For Question 1:

SELECT * FROM tableA
ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(colName, ' ', 2), ' ', -1);

For Question 2:

SELECT * FROM tableA
ORDER BY SUBSTRING_INDEX(colName, ' ', -1);

edited Dec 13, 2013 at 13:19

answered Dec 13, 2013 at 12:32

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

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

2 Comments

Nitesh Mishra Over a year ago

yes this works http://www.sqlfiddle.com/#!2/52c57/7

Kickstart Over a year ago

The first has issues and won't sort correctly if there are not sufficient delimiters. For example if there was only 1 substring for any row then it would use that substring to sort that row rather than treating it as null (or some value if required). Problem is that SUBSTRING_INDEX gets everything up to the specified index. So with 'aaa ab' if you got everything up to the 2nd space it would just return 'aaa ab', then getting the -1 index it would return 'ab' as the 3 substring when it is instead only the 2nd substring.

Kickstart · Accepted Answer · 2013-12-13 12:33:19Z

0

One way to do it:-

SELECT SampleData, IF (LENGTH(SampleData) - LENGTH(REPLACE(SampleData, ' ', '')) >= 2, SUBSTRING_INDEX(SUBSTRING_INDEX(SampleData, ' ', 2), ' ', -1), NULL) AS OrderField
FROM SampleTable
ORDER BY OrderField

Check that the field has enough delimiters to get the relevant part. If not set the sort field to NULL, but if long enough grab that part of the field. Then SORT based on it.

SQL fiddle here:-

http://www.sqlfiddle.com/#!2/8cd01/8

To get the last field is easier:-

SELECT SampleData, SUBSTRING_INDEX(SampleData, ' ', -1) AS OrderField
FROM SampleTable
ORDER BY OrderField

answered Dec 13, 2013 at 12:33

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

Collectives™ on Stack Overflow

SQL Query, Sort on Substring

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related