September 7, 2012 at 4:59 am
Can anyone tell why ltrim and rtrin function truncate value of float datatype values ?
September 7, 2012 at 5:10 am
Please provide some code which exemplifies this. And explain why you need to do this and what you are expecting.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 7, 2012 at 5:35 am
sqlnaive (9/7/2012)
Check itdeclare @test-2 float
select @test-2 = 0.1234123412341234
select @test-2
select ltrim(rtrim(@test))
Results are as follows:
0.123412341234123
0.123412
RTRIM and LTRIM both take a varchar parameter. The float value is implicitly converted to a varchar, then trimmed.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 7, 2012 at 5:44 am
For such kind of reasons we should avoid float and real datatypes ? And rather use decimal and numeric ?
September 7, 2012 at 5:50 am
sqlnaive (9/7/2012)
For such kind of reasons we should avoid float and real datatypes ? And rather use decimal and numeric ?
Have a look in BOL under CONVERT to see which of these datatypes will implicitly convert to a character datatype.
It's not a good reason to avoid float and real - it's a good reason to explicitly convert, and to know that implicit conversion can occasionally bite.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 7, 2012 at 8:38 am
ChrisM@Work (9/7/2012)
sqlnaive (9/7/2012)
For such kind of reasons we should avoid float and real datatypes ? And rather use decimal and numeric ?Have a look in BOL under CONVERT to see which of these datatypes will implicitly convert to a character datatype.
It's not a good reason to avoid float and real - it's a good reason to explicitly convert, and to know that implicit conversion can occasionally bite.
I typically don't use float for a variety of reasons but I thought I would play with this a little bit. I am completely unable to get the float value to cast or convert to a varchar and not truncate it. I was able to cast it first to a numeric and then to a varchar to keep the original number. Of course you have to be careful of the numeric precision or you get rounding errors.
declare @test-2 float
select @test-2 = 0.1234123412341234
select @test-2 as Original, CAST(@test as varchar(50)) as CastVarchar, CONVERT(varchar(50), @test-2) as ConvertVarchar, cast(CAST(@test as numeric(20,16)) as varchar(50)) as Clean, cast(CAST(@test as numeric(20,18)) as varchar(50)) as WithRoundingErrors
So two questions that maybe somebody can answer. How would you get that float value to a varchar without additional cast/convert? Why does the float value change to a smaller number when the precision increases?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 7, 2012 at 8:44 am
sqlnaive (9/7/2012)
For such kind of reasons we should avoid float and real datatypes ? And rather use decimal and numeric ?
No. Rather don't use string functions on values that are not strings.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 7, 2012 at 3:44 pm
I am completely unable to get the float value to cast or convert to a varchar and not truncate it
From BOL:
"By default, the precision of built-in operations on float data type data is six decimal places."
So you probably won't be able to get more than that in a default conversion in CAST/CONVERT.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 7, 2012 at 3:50 pm
Why does the float value change to a smaller number when the precision increases?
Maybe just the general nature of floating point values, that they are approximations only.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 7, 2012 at 4:12 pm
I'm compelled to ask WHY you even feel the need to ltrim(rtrim()) floating point columns. Did you think they might have leading or trailing spaces?
Gail has it right: Use string functions on strings.
If you want precise control over shortening the results returned from a float column, there is always the ROUND() function.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 7, 2012 at 4:17 pm
If you want precise control over shortening the results returned from a float column, there is always the ROUND() function.
If you want precise values, just use decimal to begin with. Float is inherently approximate, as BOL clearly states.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 18, 2012 at 4:31 pm
ScottPletcher (9/7/2012)
If you want precise control over shortening the results returned from a float column, there is always the ROUND() function.
If you want precise values, just use decimal to begin with. Float is inherently approximate, as BOL clearly states.
I took it as a given that float columns were already in place.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 19, 2012 at 2:01 am
Sean Lange (9/7/2012)
ChrisM@Work (9/7/2012)
sqlnaive (9/7/2012)
For such kind of reasons we should avoid float and real datatypes ? And rather use decimal and numeric ?Have a look in BOL under CONVERT to see which of these datatypes will implicitly convert to a character datatype.
It's not a good reason to avoid float and real - it's a good reason to explicitly convert, and to know that implicit conversion can occasionally bite.
I typically don't use float for a variety of reasons but I thought I would play with this a little bit. I am completely unable to get the float value to cast or convert to a varchar and not truncate it. I was able to cast it first to a numeric and then to a varchar to keep the original number. Of course you have to be careful of the numeric precision or you get rounding errors.
declare @test-2 float
select @test-2 = 0.1234123412341234
select @test-2 as Original, CAST(@test as varchar(50)) as CastVarchar, CONVERT(varchar(50), @test-2) as ConvertVarchar, cast(CAST(@test as numeric(20,16)) as varchar(50)) as Clean, cast(CAST(@test as numeric(20,18)) as varchar(50)) as WithRoundingErrors
So two questions that maybe somebody can answer. How would you get that float value to a varchar without additional cast/convert? Why does the float value change to a smaller number when the precision increases?
If you want to convert float to a string datatype, use STR(), that's what it's for:
declare @test-2 float
select @test-2 = 0.1234123412341234
select
@test-2 as Original,
CAST(@test as varchar(50)) as CastVarchar,
CONVERT(varchar(50), @test-2) as ConvertVarchar,
cast(CAST(@test as numeric(20,16)) as varchar(50)) as Clean,
cast(CAST(@test as numeric(20,18)) as varchar(50)) as WithRoundingErrors,
CAST(STR(@test, 18, 16) AS VARCHAR(50)) -- exactly matches @test-2 (STR returns CHAR)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 19, 2012 at 8:07 am
ChrisM@Work (9/19/2012)
Sean Lange (9/7/2012)
ChrisM@Work (9/7/2012)
sqlnaive (9/7/2012)
For such kind of reasons we should avoid float and real datatypes ? And rather use decimal and numeric ?Have a look in BOL under CONVERT to see which of these datatypes will implicitly convert to a character datatype.
It's not a good reason to avoid float and real - it's a good reason to explicitly convert, and to know that implicit conversion can occasionally bite.
I typically don't use float for a variety of reasons but I thought I would play with this a little bit. I am completely unable to get the float value to cast or convert to a varchar and not truncate it. I was able to cast it first to a numeric and then to a varchar to keep the original number. Of course you have to be careful of the numeric precision or you get rounding errors.
declare @test-2 float
select @test-2 = 0.1234123412341234
select @test-2 as Original, CAST(@test as varchar(50)) as CastVarchar, CONVERT(varchar(50), @test-2) as ConvertVarchar, cast(CAST(@test as numeric(20,16)) as varchar(50)) as Clean, cast(CAST(@test as numeric(20,18)) as varchar(50)) as WithRoundingErrors
So two questions that maybe somebody can answer. How would you get that float value to a varchar without additional cast/convert? Why does the float value change to a smaller number when the precision increases?
If you want to convert float to a string datatype, use STR(), that's what it's for:
declare @test-2 floatselect @test-2 = 0.1234123412341234
select
@test-2 as Original,
CAST(@test as varchar(50)) as CastVarchar,
CONVERT(varchar(50), @test-2) as ConvertVarchar,
cast(CAST(@test as numeric(20,16)) as varchar(50)) as Clean,
cast(CAST(@test as numeric(20,18)) as varchar(50)) as WithRoundingErrors,
CAST(STR(@test, 18, 16) AS VARCHAR(50)) -- exactly matches @test-2 (STR returns CHAR)
Ahh yes...if I used floats I would probably remember that function. I knew there had to something. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply