Short Db Field

[emmim44]

Hi..I have a db field called "sheetNo" it contains smth like "1,2,3,4,5,Cover,1B,2C,9,20,21 "

When short it by Asc or desc in my query, it doenst sort...what might be wrong ??

Thank u all..

[Dan_Bracuk]

The problem is that you are storing numbers as text. When you do that '9' is greater than '10' .

[Dan_Bracuk]

The problem is that you are storing numbers as text. When you do that '9' is greater than '10' .

[paross1]

Depending on your database, you may be able to perform some gymnastics using CASE and functions to give you a sort order that is more in line with your expectations.

Example:

SELECT sheetNo
FROM your_table
WHERE whatever
ORDER BY CASE
WHEN IsNumeric(sheetNo) <> 0
THEN CAST(sheetNo AS NUMERIC)
ELSE 0 END,
UPPER(sheetNo)

Phil

[emmim44]

I will try

[emmim44]

I am using oracle..I think isnumeric is not an oracle function..what is the equivalent..

ORDER BY CASE WHEN IsNumeric(ctrDocs.sheetno)<> 0 THEN
cast(ctrDocs.sheetno AS NUMERIC) ELSE 0 END

[Dan_Bracuk]

to_number

[emmim44]

I m still getting err.. What I am doing wrong..it is oracle..
ORDER BY CASE WHEN TO_NUMBER(ctrDocs.sheetno)<> 0 THEN
cast(ctrDocs.sheetno AS NUMERIC) ELSE 0 END

[paross1]

Ah yes, Oracle. The to_number() function will throw an Oracle ORA-01722: invalid number error if you try to use it on anything non-numeric, so it really can't be used in this way because you have no way to trap the exception. Since there is no "native" isnumeric() function, you have to work around...

Borrowing from Oracle/PLSQL: Test a string for a numeric value, something like this might get you in the ballpark...

SELECT sheetNo
FROM ctrDocs
WHERE whatever
ORDER BY CASE
WHEN LENGTH(TRIM(TRANSLATE(ctrDocs.sheetno, '.0123456789',' '))) IS NULL
THEN CAST(ctrDocs.sheetno AS NUMBER)
ELSE 0 END, ctrDocs.sheetno

Phil

[emmim44]

Thank you all.