Ordering with integers and text 1

[bojan89]

One of the fields that I need displayed is called ProductValue. Depending on the restrains of the Product, the ProductValue can either be an integer or text. I want the resulting table to be ordered by value if it can be converted to integer, or alphabetically if it cannot. Currently I am have

Select *
from table t1
where table1.Product = 'box'
Order by cast(table1.ProductValue as int)

This query works fine, but when I change "box" to "paint"
it causes an error "Invalid Number".

with no restrains the table looks like

Product | ProductValue
-----------------------
box | 11111
box | 22222
paint | blue
paint | red
paint | green

Note: I am using Oracle.

The question is How can I order it by value if ProductValue can be converted to integer, or alphabetically if it cannot.

 

[jbenson001]

Note: I am using Oracle.

This is a SQL Server forum.

 

[gmmastros]

You should post this question in one of the oracle forums, since this forum is specifically for Microsoft SQL Server.

In SQL Server, I would check to see if the number is numeric. If it is, I would left pad with 0's and sort on that.

Select * 
From   table1
where  table1.Product = 'box'
Order By Case When IsNumeric(ProductValue) = 1 
              Then Right('000000000000000000' + ProductValue, 20)
              Else ProductValue
              End

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom