OrderBy question

[ndoggy020]

Hello.

I am trying to sort my results via the account number field.

I am sorting in descending order but the kicker is, the account numbers can contain letters as well. For example:

123456ABC1

What I am seeing in my results is this for example: (remember in DESC order)

123456ABC
1222
111234DEF

I dont undestand how that '1222' could be right there. Any ideas on what is wrong or how to correct this?

Thanks

[amitpatel66]

Try this

[code=oracle]

SELECT * FROM table_name ORDER BY REPLACE(TRANSLA TE(UPPER(column _name),'ABCDEFG HIJKLMNOPQRSTUV WXYZ',' '),' ','')

[/code]

[ndoggy020]

It did not work. Please help.

Thanks for the try.

[ndoggy020]

I think my problem is that this field I am trying to order by is a varchar and not truly numeric. This could be the cause.

[amitpatel66]

I think my problem is that this field I am trying to order by is a varchar and not truly numeric. This could be the cause.

Try this:

[code=oracle]

SELECT col1,col2,col3 FROM
(SELECT col1,col2,col3, REPLACE(TRANSLA TE(UPPER(column _name),'ABCDEFG HIJKL MNOPQRSTUVWXYZ' ,' '),' ','') num FROM table_name) ORDER BY num

[/code]