December 26, 2006 at 1:59 am
hi to every one.
i am facing a problem in my sql query. first i let you know about the question.
there is a field Rate Numeric(18,4) and in this numeric field i insert 2.5, 10, 20, 2.05, 9.63, 65.9863 etc.
when i use select query it give me the following output.
2.5000, 10.0000, 20.0000, 2.0500, 9.6300, 69.9863 etc.
but i want output in the following ways.
2.5, 10 , 20, 2.05, 9.63, 65.9863 etc.
as i insert the data in this field i want the same output ..
which query should i apply for the same problem.
i tried this by using case when then , but i am not get the exact result.
if any one can resolve this problem then suggest query for that.
December 26, 2006 at 4:15 am
select convert(float,rate)
however float is an approximation, so beware the type conversion - you may find some = operations do not return the results you would expect.
you should also be aware of the scale and precision of floats - although looking at the data you enter do you really need numeric(18,4) - that takes you to a maximum value of
99999999999999.9999
ninety nine thousand nine hundred and ninety nine billion nine hundred and ninety nine thousand nine hundred and ninety nine million nine hundred and ninety nine thousand nine hundred and ninety nine point 999 - i think thats right
( i can't beleive i just typed that)
MVDBA
December 26, 2006 at 4:31 am
this is not quit helpful to me but i am quite happy for your response.
when i check this logic i got some unsatified result as output of this ...
i tested it as
declare @a as numeric(18,4)
Set @a = .10
select convert(float,@a)
output = 0.10000000000000001
declare @a as numeric(18,4)
Set @a = 2.10
select convert(float,@a)
output = 2.1000000000000001
declare @a as numeric(18,4)
Set @a = 2.
select convert(float,@a)
output = 2.0
declare @a as numeric(18,4)
Set @a = 2.369
select convert(float,@a)
output = 2.3690000000000002
just i want to get the same result as i inserted into this field.
December 26, 2006 at 4:58 am
using convert(real,0.10) will give you the correct answer, but only because the precision is lower. - you may still get the same problem...
you might try creating this as an inline function
declare @decloc int
declare @intval varchar(18)
declare @decval varchar(5)
set @decloc=(select charindex('.','0.0100'))
set @intval=floor(0.0100)
select @decval=substring('0.0100',@decloc+1,18)
--select @decval
if right(@decval,4)='0000' set @decval=''
if right(@decval,3)='000' set @decval='.'+left(@decval,1)
if right(@decval,2)='00' set @decval='.'+left(@decval,2)
if right(@decval,1)='0' set @decval='.'+left(@decval,3)
select @intval+@decval
MVDBA
December 27, 2006 at 12:43 am
You do not say in your post why you need to get back out the exact representation of the number that you put in. But here's one solution: store and retrieve the value as text (either instead of the numeric value, or in another column along with it). The text will not change.
If you need to do some numberic processing with the value after retrieving, you can convert from text to numeric format after retrieving it, and\or use the text as a format pattern to massage the numeric into the same format it was in before.
December 27, 2006 at 1:25 am
Hi John Boyce
your suggestion is good enough, but i want the same result using numeric field. that is why i ask this question to all. actually this converstion and the exact match i got using val function of vb.net 2.0 in my front end coding but for this i use a loop in my code for filling each row in my data grid. but instand of doing this thing i just want to modify the query and apply some function so i avoid loop in my coding.
if you found any help of source then let me know . so that others can help full by your suggestion....
shashi kant
December 27, 2006 at 8:15 am
Why do you need the data in the exact textual format that you inserted it with?
SQL returns the values 2.5000, 10.0000, 20.0000, 2.0500, 9.6300, 69.9863 as a representation of the values based on the field definition. Note, it is just a formatted representation of the value, 2.5000 is really 2.5. If you are doing numeric comparisons / math this value is what you need.
If you are doing textual operations, write a function to remove the trailing zeros (and potentially the period / decimal-point as well). Such as
SELECT dbo.fn_RemoveTrailingZeroes(Rate) AS Rate
If you want the result back as a number-dependant decimal format NUMERIC (18,0) / (18,1) / (18,2) / (18,3) / (18,4) you are going to have to write a CASE statement that is dependant on the number of decimal places the number needs. Such as
SELECT CASE (dbo.fn_NumDecPlaces(Rate))
WHEN 0 THEN CAST(Rate AS Numeric(18,0))
WHEN 1 THEN CAST(Rate AS Numeric(18,1))
WHEN 2 THEN CAST(Rate AS Numeric(18,2))
WHEN 3 THEN CAST(Rate AS Numeric(18,3))
WHEN 4 THEN CAST(Rate AS Numeric(18,4))
END AS Rate
December 27, 2006 at 8:24 am
As you can see, this gets you very close. With some case logic you can delete the decimal points from 10. and 20. And I assume you want numbers less than zero to appear as shown.
select replace(rtrim(replace(convert(varchar,f),'0',' ')),' ','0') from t
2.5
10.
20.
2.05
9.63
69.9863
0.2
December 27, 2006 at 8:34 am
So how about if you do this:
select
convert ( real ,2.5000 )
select
convert ( real ,10.0000)
select
convert ( real ,20.0000)
select
convert ( real ,2.0500 )
select
convert ( real ,9.6300 )
select
convert ( real ,69.9863)
And this will be the result:
2.5
10
20
2.05
9.63
69.9863
December 27, 2006 at 8:38 am
If 'real' does that, that's really cool.
December 27, 2006 at 1:27 pm
Good points Joe, but I think that you are a bit too harsh, possibly bordering on acerbic.
Should I begin to laud you on your straight forward grammar and then close with a salvo lambasting you for your lack of usage of spell checker ?
Think about it !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
December 27, 2006 at 2:56 pm
Any conversion you do from numeric to float or real will change your data values. You could convert to varchar and do string manipulation to strip trailing zeroes, but T-SQL is not the best language to do it in. It can be done if you insist:
SELECT
s, ltrim(left(s, len(s) + CASE WHEN s LIKE '%.0000' THEN -5 ELSE 1 - patindex('%[^0]%', reverse(s)) END))
FROM (
SELECT str([numeric field],20,4) AS s FROM
) x
This could be done more easily in .NET with a RegEx object that replaces the pattern "[.]?0+$" with an empty string. If you were absolutely determined to do this on the SQL server you could create a CLR function that uses RegEx, but I would have to say I agree with Joe that you should do your formatting in the presentation layer.
January 1, 2007 at 2:59 pm
"REAL" certainly doesn't work that way on my box...
select convert ( real ,2.5000 )
select convert ( real ,10.0000)
select convert ( real ,20.0000)
select convert ( real ,2.0500 )
select convert ( real ,9.6300 )
select convert ( real ,69.9863)
------------------------
2.5
------------------------
10.0
------------------------
20.0
------------------------
2.05
------------------------
9.6300001
------------------------
69.986298
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply