x

Sort coulmn with alphanumeric values in a table

Hi,

I have a column where the data contains both alphabets and numbers.

Sample data is as follows:

1st Qtr 2008

1st Qtr 2009

2nd Qtr 2008

2nd Qtr 2009

3rd Qtr 2008

3rd Qtr 2009

I would like to sort this data and look it as follows:

1st Qtr 2008

2nd Qtr 2008

3rd Qtr 2008

1st Qtr 2009

2nd Qtr 2009

3rd Qtr 2009

Any suggestions are welcome.

Regards, BI DWH BALA

more ▼

asked Nov 30, 2009 at 02:09 PM in Default

BI DWH BALA gravatar image

BI DWH BALA
606 43 60 62

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

In fact, why not just sort on the substring expressions?

order by substr(chrcol,9,4), /* your year */
    substr(chrcol,1,1) /* your Q number */

which would work as long as you kept to that format or we reach the year 10,000.

Update:

While I'm thinking about it, I should add that you could also sort on a CASE expression.

Here's an example:

create table annsales
    (quarter varchar2(10),
    year int,
    revenue float);

insert INTO annsales values ('First',2009,26.0);
insert INTO annsales values ('Second',2009,29.0);
insert INTO annsales values ('Third',2009,30.0);
insert INTO annsales values ('Fourth',2009,29.0);
insert INTO annsales values ('First',2008,20.0);
insert INTO annsales values ('Second',2008,25.0);
insert INTO annsales values ('Third',2008,24.0);
insert INTO annsales values ('Fourth',2008,27.0);

Now, this probably doesn't produce rows in the order you want:

select  *
from    annsales
order by year, quarter;

But this does order by quarter, as opposed to the name of the quarter:

select  *
from    annsales
order by year,
    case Quarter
      when 'First' then 1
      when 'Second' then 2
      when 'Third' then 3
      when 'Fourth' then 4
      END;

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.

case STATUS  when 'E' then 0  when 'A' then 1  when 'X' then 2 ...  else -1 end /* unkown statuses shouldn't happen, put them ahead of E's */ 
more ▼

answered Dec 01, 2009 at 02:34 PM

dhathewa gravatar image

dhathewa
36 1

Thank you Dhatewa. This way seems to be simple and short.
Dec 01, 2009 at 02:47 PM BI DWH BALA
(comments are locked)
10|1200 characters needed characters left
CREATE TABLE t1 (chrcol VARCHAR(20),data NUMBER)
INSERT INTO t1 VALUES('1st Qtr 2008',50)
INSERT INTO t1 VALUES('2nd Qtr 2008',40)
INSERT INTO t1 VALUES('3rd Qtr 2008',30)
INSERT INTO t1 VALUES('4th Qtr 2008',20)
INSERT INTO t1 VALUES('1st Qtr 2009',30)
INSERT INTO t1 VALUES('2nd Qtr 2009',40)
INSERT INTO t1 VALUES('3rd Qtr 2009',50)
INSERT INTO t1 VALUES('4th Qtr 2009',60)


 SELECT chrcol,data FROM 
     (SELECT chrcol,data,substr(chrcol,1,7) AS quarter, 
             substr(chrcol,8,5) AS year FROM t1) 
 ORDER BY year,quarter

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

more ▼

answered Dec 01, 2009 at 08:47 AM

Andrew Mobbs gravatar image

Andrew Mobbs
1.5k 1 3

(comments are locked)
10|1200 characters needed characters left

You could also use a function.

CREATE OR replace FUNCTION my_sort_order
(qtr_expression VARCHAR2)
RETURN VARCHAR2 DETERMINISTIC IS
V_RETVAL VARCHAR2(5);
BEGIN
V_RETVAL := substr(qtr_expression,9,4) || substr(qtr_expression,1,1);
RETURN V_RETVAL;
END my_sort_order;

CREATE TABLE t1 (chrcol VARCHAR(20),data NUMBER)       ;
INSERT INTO t1 VALUES('1st Qtr 2008',50) ;
INSERT INTO t1 VALUES('2nd Qtr 2008',40) ;
INSERT INTO t1 VALUES('3rd Qtr 2008',30) ;
INSERT INTO t1 VALUES('4th Qtr 2008',20) ;
INSERT INTO t1 VALUES('1st Qtr 2009',30) ;
INSERT INTO t1 VALUES('2nd Qtr 2009',40);
INSERT INTO t1 VALUES('3rd Qtr 2009',50) ;
INSERT INTO t1 VALUES('4th Qtr 2009',60);

SELECT * FROM t1
ORDER BY my_sort_order(chrcol);

This worked but I had to re-edit it to post - may require further checking.

more ▼

answered Dec 01, 2009 at 02:59 PM

KillerDBA gravatar image

KillerDBA
1.5k 8 9 10

Yep KillerDBA. This solution also works. But only thing, we need to create a userdefined function to do this. And it will increase the number of fucntion calls. I appreciate your response. Thank you.
Dec 01, 2009 at 03:02 PM BI DWH BALA

True. I have to admit, I don't know the performance implications of using functions heavily, although I think it's not bad. The reason I suggested a function was for flexibility.

Suppose, the string wasn't always 11 characters and you had to search back to find the last space before the year or had to do something special to parse the Quarter number. In such a case, the order by clause in your select statement start to get ugly and a function would look much cleaner.
Dec 01, 2009 at 03:43 PM KillerDBA
(comments are locked)
10|1200 characters needed characters left

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;
more ▼

answered Mar 28 at 11:19 AM

mgayathrinatarajamani gravatar image

mgayathrinatarajamani
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x378

asked: Nov 30, 2009 at 02:09 PM

Seen: 1767 times

Last Updated: Mar 28 at 11:19 AM