CODEWITHSUNDEEP
oracle-database
HiDayurie Dave
HiDayurie Dave

Reputation: 1807

Select order by letter with number in Oracle

I have records like this:

LEGO 9
LEGO 4
LEGO 5
LEGO 7
LEGO 12
LEGO 13
LEGO 14
LEGO 15
LEGO 10
LEGO 11
LEGO 3
LEGO 1
LEGO 2
LEGO 6
LEGO 8

I want them to be sorted ASC

LEGO 1
LEGO 2
LEGO 3
LEGO 4
LEGO 5
LEGO 6
LEGO 7
LEGO 8
LEGO 9
LEGO 10
LEGO 11
LEGO 12
LEGO 13
LEGO 14
LEGO 15

When I use this statement query below, sort is not correct:

SELECT * FROM WA_LEG_TBL_LINES ORDER BY LINENAME ASC

LEGO 1
LEGO 10
LEGO 11
LEGO 12
LEGO 13
LEGO 14
LEGO 15
LEGO 2
LEGO 3
LEGO 4
LEGO 5
LEGO 6
LEGO 7
LEGO 8
LEGO 9

Is there any wrong with the statement? I want to order it by LINENAME ASC.

Upvotes: 0

Views: 30

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39497

That is how string sorting works.

If you have text LEGO in all the rows in the column, you can remove and cast the result to number and sort by it.

select *
from WA_LEG_TBL_LINES
order by cast(replace(linename, 'LEGO ') as number);

Upvotes: 1

Related Questions