CODEWITHSUNDEEP
mysqlsqlsql-order-by
rfpdl
rfpdl

Reputation: 964

order by a some part of the string

I have these value in generated_code column

NEW 6y0m
!EW 0y8m
N!W 5y11m
!EW 17y0m

I would like to order it ascending based on the year which is located after the space and before the 'y'. outcome should be something like this

!EW 17y0m
NEW 6y0m
N!W 5y11m
!EW 0y8m

i tried this, but it returns empty set

select
   mid(generated_code,4,LOCATE(generated_code,'y')-6) 
from
   classifier_bahrain 
order by
   mid(generated_code,4,LOCATE(generated_code,'y')-6) desc

Upvotes: 0

Views: 77

Answers (1)

Raging Bull
Raging Bull

Reputation: 18747

Parameters for locate function should be in reverse order. Also if you try to order by the substring, it will be in the order 6,5,17,0.
So convert it to int before ordering the result.

Try this:

SELECT generated_code,SUBSTR(generated_code,4,locate('y',generated_code)-4) as ORDERBY
FROM classifier_bahrain 
ORDER BY CAST(SUBSTR(generated_code,4,locate('y',generated_code)-4) AS UNSIGNED) DESC

Result:

GENERATED_CODE  ORDERBY
!EW 17y0m       17
NEW 6y0m        6
N!W 5y11m       5
!EW 0y8m        0

See result in SQL Fiddle

Upvotes: 2

Related Questions