Search code examples
sql-servercsvbcp

Special character when exporting SQL query results to CSV

I exported my SQL query results into a CSV file using the BCP command. All my records are properly exported and formatted except only one record having special characters at the end like B0013467?.

My SQL record doesn't have this character in its value. But how come it got amended while exporting results to the CSV file? I even opened with Notepad. The special character still remained in exported results.

Please advise. My BCP command is like this:

bcp "select * from ##OutputTable3" queryout "' + @Path + '\Temp_RawData3.csv" -S ' + @@SERVERNAME + ' -T -t , -c

Thanks!

Solution

  • check the specific column for the special character using the following code ,which will give you the Hex values of the characters.

    cast (MyColumn as varbinary(max))

    0x4300300030003000310036003900390033000E20

    https://en.wikipedia.org/wiki/Left-to-right_mark