Urgent need for this query

Question

Post reply

Urgent need for this query

manutosh pandey

SSC-Addicted

Points: 488
More actions
September 12, 2005 at 10:00 pm

#88980

Hi all,
        I have a urgent need in query if I have a number 20.160 it will show 20.16 means 0 should not be there.But if 20.106 the number should be displayed full. trailing zeros should be removed.
        Please help me its urgent.
                                       Million of thanks in advance.
                                               Manutosh

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Ninja's_RGR'us

SSC Guru

Points: 294069

September 12, 2005 at 10:16 pm

#589224

What's the datatpye of the column?

manutosh pandey SSC-Addicted Points: 488 More actions · Answer 2

The datatype of that column is int but we change it into varchar.

Thanks in advance.

Manutosh

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

with int datatype you don't have decimals

you could do

select convert(decimal(11,3),yourIntCol) / 100 as youDecCol

(adjust the 11 to your needs !)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

manutosh pandey SSC-Addicted Points: 488 More actions · Answer 4

Hi ,

It is still I found 0 in tha last.Suppose I give 20.160 the result should be 20.16 not include 0 at last.

Thanks

Manutosh

faizjmc SSC Eights! Points: 881 More actions · Answer 5

faizjmc

SSC Eights!

Points: 881

September 13, 2005 at 3:11 am

#589277

alzdba query is working properly

manutosh pandey SSC-Addicted Points: 488 More actions · Answer 6

Hi,

I have given this query

select convert(decimal(11,3),20.160)/100

result- .2016000

I need only 20.16

Please help me its urgent. I have given this query to my client.

Thanks

Manutosh

manutosh pandey SSC-Addicted Points: 488 More actions · Answer 7

Hi,

I have given this query

select convert(decimal(11,3),20.160)/100

result- .2016000

I need only 20.16

Please help me its urgent. I have given this query to my client.

Thanks

Manutosh

philcart SSC-Forever Points: 47794 More actions · Answer 8

The presentation format of the data should be done on the client that is displaying the data. eg: Excel, Crystal Reports, Reporting Services, etc...

This is very clunky and NOT what SQL Server should be doing, but I though it would be interesting to try it out.

DECLARE @result decimal(11,6)
SELECT @result = 20.1 -- test 1
SELECT @result, SUBSTRING(CAST(@result as varchar(30)), 1, LEN(@result)-PATINDEX('%[1-9]%', REVERSE(@result)) + 1)
SELECT @result = 20.16 -- test 2
SELECT @result, SUBSTRING(CAST(@result as varchar(30)), 1, LEN(@result)-PATINDEX('%[1-9]%', REVERSE(@result)) + 1)
SELECT @result = 20.106 -- test 3
SELECT @result, SUBSTRING(CAST(@result as varchar(30)), 1, LEN(@result)-PATINDEX('%[1-9]%', REVERSE(@result)) + 1)
SELECT @result = 20.1006 -- test 4
SELECT @result, SUBSTRING(CAST(@result as varchar(30)), 1, LEN(@result)-PATINDEX('%[1-9]%', REVERSE(@result)) + 1)

Interesting to note that the decimal variable doesn't require explicit casting to varchar for LEN, REVERSE and PATINDEX, but it is required for SUBSTRING

--------------------
Colt 45 - the original point and click interface

manutosh pandey SSC-Addicted Points: 488 More actions · Answer 9

Hi

Thanks Phill

It works fine

Manutosh