Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

format - sqlcmd for SQL Server removes zeros to the left of the decimal point

We're trying to read data from a SQL Server table for inserting it into a .csv using sqlcmd in Linux OS.

Unfortunately when we try to read the float values from table like

-0.373783

then in files we get

-.373783

but we would like to get on file -0.373783 as on the database table.

We don't understand why sqlcmd remove leading zeros, I mean to the left of the decimal point, the same select query on SSMS return the right number representation.

We tried to use the following statement

SELECT CAST(floatField AS Numeric(9,6))

but we keep getting the number without the zeros before the decimal point.. we are going crazy!

Thanks in advance for your suggestion

question from:https://stackoverflow.com/questions/65907397/sqlcmd-for-sql-server-removes-zeros-to-the-left-of-the-decimal-point

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Please try the following.

The FORMAT() function is using .Net CLR formatting. Combination of '0's and '#'s will give you what you are looking for.

SQL

DECLARE @var DECIMAL(10,6) = -0.373783;

SELECT @var AS [Before]
    , CAST(@var AS VARCHAR(12)) AS [After]
    , FORMAT(@var,'###0.######') AS [Formatted];

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...