Link to home
Start Free TrialLog in
Avatar of jsctechy
jsctechyFlag for United States of America

asked on

format number depending on the decimal spaces

hi,
is there a way to format a field depending on the decimal places? lets said for instance i have a number like this: 8 and another on the second row like this 8.5 the data field for this number is decimal so by definition my first number will display 8.00 and the second one 8.50 which is right. What i need to do is to display the following instead:
where there is not decimal value them just show the whole number and where the number does have a decimal then display the decimal value thus, my result should look like this:
8
8.50
how can i achieved this? can this be include on a function and reuse many times i want?
thanks for your input.-jsctechy
Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image

Which DB is this req. for - SQL Server / MySQL / Oracle ?
Avatar of Aneesh
Hello jsctechy,

SELECT CASE WHEN CAST(urColumn AS INT) = urColumn THEN CAST(urColumn AS INT) ELSE CAST( ROUND(urColumn,2 )AS VARCHAR) END

GOD Bless,
Aneesh R.
Avatar of jsctechy

ASKER

is SQL 2000.

is there a way to include this on a function a have a huge query and add this two lines will be too much and difficult to read
that solution you have provide me does not work it gives me this error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '8.50' to a column of data type int.
How about this :

SELECT CASE WHEN CHARINDEX(CAST(urColumn AS VARCHAR),'.') <> 0 THEN CAST(urColumn AS INT)
    ELSE CAST(ROUND(urColumn,2) AS VARCHAR) END
ASKER CERTIFIED SOLUTION
Avatar of ee_rlee
ee_rlee
Flag of Philippines image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial