Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Issue in inserting float value in a nvarchar column

Answered Issue in inserting float value in a nvarchar column

  • Wednesday, January 30, 2013 7:20 AM
     
     
    Sign In to Vote
    0

    i am using sql server 2008.

    one of my table column is of float type and is having a value 12345.67

    i am inserting this value into a stage table , with type nvarchar(50), before printing it into a dat file.

    the issue is, in the output file, the value is truncated as 12345.6 instead of 12345.67 .

    How to resolve this? Thanks in advance.

    Vijay

     

All Replies

  • Wednesday, January 30, 2013 7:23 AM
     
     
    Sign In to Vote
    0

    Hi,

    Check the length of the variable you are stroing

    PS.Shakeer Hussain

     
  • Wednesday, January 30, 2013 7:40 AM
     
     
    Sign In to Vote
    0

    Try

    select cast(cast(12345.67 as decimal(12,2)) as varchar)
    go

    to do the insert.

    Many Thanks & Best Regards, Hua Min


     
  • Wednesday, January 30, 2013 8:25 AM
     
     
    Sign In to Vote
    0
    use the str() function to format the data. Books Online has all the details.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

     
  • Wednesday, January 30, 2013 9:31 AM
     
     Answered Has Code
    Sign In to Vote
    1

    Converting values from one object type to a string without an explicit conversion is always a dangerous activity as the system does not know how to format the resulting string. A floating point number can be virtually any length with the decimal point in any place so SQL deals with it as best as it can but if you want it to appear a certain way then you will need to use the STR command to create that format, so if you want it to 2 decimal places you will need to use:

    SELECT STR(12345.67, 19, 2)

    There are a couple of problems with this though, first it'd create whitespace in front of the number to fill to the correct size

    "           12345.67"

    and second when using with a number which has less than the number of defined decimal places you will end up with trailing zeros:

    SELECT STR(12345.67, 19, 3)

    "          12345.670"

    You can easily get rid of the leading white space:

    SELECT LTRIM(STR(12345.67, 19, 3))

    "12345.670"

    but the trailing zeros can be more of an issue, you can fairly easily design a function to drop these if you need to though

    CREATE FUNCTION dbo.udfSTR(@F FLOAT, @L INT, @D INT)
RETURNS VARCHAR(255)
AS
BEGIN
	DECLARE @S VARCHAR(255);

	SET @S = LTRIM(STR(@F, @L, @D));
	
	WHILE @S LIKE '%[0\.]' ESCAPE '\' AND LEN(@S) > 1
		SET @S = SUBSTRING(@S, 1, LEN(@S) - 1);
	
	RETURN @S;
END

    SELECT dbo.udfSTR(12345.67, 19, 3);


    ---
    Shaun Turner

    My Blog | My LinkedIn

    If you're in the UK please join The SQL Developers / DBA's user group for the UK on LinkedIn


     
  • Wednesday, January 30, 2013 11:30 AM
     
     
    Sign In to Vote
    0

    Thanks uaMin Chen..

    The insertion into the stage table, before generating the output file is showing the correct column value as 12345.67.

    but i am generating an output file , from this stage table using the below query.

    SET @Query = 'bcp DBName.dbo.StageTableName out ' + ' -c -T'
    EXEC @Result = Master..XP_CMDSHELL @Query, NO_OUTPUT

    but unfortunately, the generated output is showing wrong values at the end like 

    65.609999999 instead of 65.61 etc...

    Kindly help me out.

    Vijay

     
  • Wednesday, January 30, 2013 12:50 PM
     
     Proposed
    Sign In to Vote
    0

    Again, if you want a certain format of your float data, you need to use the str() function. Float is an imprecise data type, and you cannot store 65.61 exactly in a float, only something which is very close.

    To use str(), you would need to have a query and use the queryout option. Or define a view. Or maybe you should go for an SSIS package. After all, any security-minded DBA would refuse to enable xp_cmdshell.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

     
  • Wednesday, January 30, 2013 3:27 PM
     
     
    Sign In to Vote
    0

    Thanks uaMin Chen..

    The insertion into the stage table, before generating the output file is showing the correct column value as 12345.67.

    but i am generating an output file , from this stage table using the below query.

    SET @Query = 'bcp DBName.dbo.StageTableName out ' + ' -c -T'
    EXEC @Result = Master..XP_CMDSHELL @Query, NO_OUTPUT

    but unfortunately, the generated output is showing wrong values at the end like 

    65.609999999 instead of 65.61 etc...

    Kindly help me out.

    Vijay

    Try

    SET @Query = 'bcp DBName.dbo.StageTableName out ' + ' -c -T'
    EXEC @Result = Master..XP_CMDSHELL @Query, NO_OUTPUT

    set @Result=round(@Result,2)

    Many Thanks & Best Regards, Hua Min

     
  • Wednesday, January 30, 2013 4:26 PM
     
     
    Sign In to Vote
    0

    SET @Query = 'bcp DBName.dbo.StageTableName out ' + ' -c -T'

    EXEC @Result = Master..XP_CMDSHELL @Query, NO_OUTPUT

    set @Result=round(@Result,2)

    Huh? The return value from xp_cmdshell is an integer value, so there is little reason to round it. And Vijay's problem is when he writes data to the file. HuaMin, may suggest that you read the questions before you try to answer? :-)

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

     
  • Thursday, January 31, 2013 1:35 AM
     
     
    Sign In to Vote
    0

    Thanks uaMin Chen..

    The insertion into the stage table, before generating the output file is showing the correct column value as 12345.67.

    but i am generating an output file , from this stage table using the below query.

    SET @Query = 'bcp DBName.dbo.StageTableName out ' + ' -c -T'
    EXEC @Result = Master..XP_CMDSHELL @Query, NO_OUTPUT

    but unfortunately, the generated output is showing wrong values at the end like 

    65.609999999 instead of 65.61 etc...

    Kindly help me out.

    Vijay

    Thanks Erland.

    Vijay,

    Then try to round the relevant value to 2 decimal points if you can.

    Many Thanks & Best Regards, Hua Min