Welcome to OGeek Q&A 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 Reply

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];

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...