bcp out: bcp exports an empty string (not null) and writes nul in the file

邓炜 21 Reputation points
2020-11-27T10:16:56.693+00:00

43247-image.png

This is the data in the table
43220-image.png
Use bcp out to export data,Export empty string as nul,and Decimal type data (0) loses the first 0,How to make the empty string not be exported to null, and the decimal number '0' does not lose the first 0?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,124 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 113.3K Reputation points MVP
    2020-11-27T14:32:31.053+00:00

    BCP was originally designed to move data between SQL Server databases. And it that is what you are doing, then it does not really matter what you get. The NUL characater will become an empty string when you import it etc.

    If you want a certain format when you extract data with BCP, you need to convert it to strings with the desired format in your query. But since you have a C++ program already, it's probably better to just run a query and format client-side.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Viorel 118.4K Reputation points
    2020-11-27T10:44:25.587+00:00

    Probably you see the data in different order. To avoid confusions, try some unique values in the first column, like 50, 51, 52.

    How did you get the second image? Maybe 0 is displayed as .00 by the viewer, but the file contains 0. Did you use a simple text editor like Notepad? Give some details.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.