Sorting in dataGrid

[simon]

I have column of dataType varchar(10).

The values in this column are decimal numbers, 'NUL', OR ''.
(because of 'NUL' and '' values I haven't defined column as decimal(18,2))

When I fill dataGrid and user click sorting on this column, the column is
sorted alphabetically.

So 100,00 is before 2,00.

Is there any option to include somehow, custom sorting, which exclude the
'NUL' and '' fields and sort other fields like numbers?

Thank you for your answer,
Simon

 

[Guest]

Hi Simon,

Suppose the column name is charNum, you can use following query

SELECT * FROM TABLE_NAME
ORDER BY (CASE WHEN charNum IS NULL OR charNum ='' THEN Big_Number ELSE
CAST(charNum AS DECIMAL(18,2)) END)

HTH

Elton Wang
(e-mail address removed)

 

[simon]

Thanks Elton,

that would be one solution.

Other is that I create invisible column in datagrid, which is decimal
dataType and use order by that column.

regards,
Simon