CODEWITHSUNDEEP
mysqlsqlselectsql-order-bysubstring
planet260
planet260

Reputation: 1454

SQL Query, Sort on Substring

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..

Upvotes: 1

Views: 4028

Answers (2)

Saharsh Shah
Saharsh Shah

Reputation: 29051

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

Upvotes: 2

Kickstart
Kickstart

Reputation: 21513

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

Upvotes: 0

Related Questions