I have a column where the data contains both alphabets and numbers.
Sample data is as follows:
I would like to sort this data and look it as follows:
Any suggestions are welcome.
Regards, BI DWH BALA
asked Nov 30 '09 at 02:09 PM in Default
BI DWH BALA
In fact, why not just sort on the substring expressions?
which would work as long as you kept to that format or we reach the year 10,000.
While I'm thinking about it, I should add that you could also sort on a CASE expression.
Here's an example:
Now, this probably doesn't produce rows in the order you want:
But this does order by quarter, as opposed to the name of the quarter:
If you did have that ordering problem, it might be worthwhile to have a function "Ordinal_Text_to_Int" that handles the cases to tenth place for you ('First', 'Second',... 'Tenth').
I usually use a technique like this when we have a bunch of status codes ('A','X','E','C'... whatever) and I want to see the output sorted with the most critical status ('E' for error, maybe) first.
(I haven't got an Oracle database to hand to test this, so there may be some minor error in syntax, but the approach should be obvious)
answered Dec 01 '09 at 08:47 AM
You could also use a function.
This worked but I had to re-edit it to post - may require further checking.
answered Dec 01 '09 at 02:59 PM
Insert into test(id,name,year)value ('1st','qtr',2008), ('1st','qtr',2009), ('2nd','qtr',2008), ('2nd','qtr',2009), ('3rd','qtr',2008), ('3rd','qtr',2009);Select * from test order by year asc;
answered Mar 28 at 11:19 AM