CODEWITHSUNDEEP
sqlsql-serverunicodeencodingascii
user1893148
user1893148

Reputation: 2190

Troublesome Character in SQL Table?

I'm pulling records from a medium-sized table (several million records), stored in MS SQL Server. The result set has ~30k rows, but I can't get it to write to .csv or .xslx cleanly. One of the fields is a string. And several hundred of the rows have some character in that string field that causes a line break or a column break, which prevents me from duplicating the well formed table.

When the result set is displayed in Management Studio, whatever character is causing this appears to be a tab or several spaces in a row. However, it seems to be some other character because replacing several spaces or the tab (etc) in the select statement does nothing to change either the appearance of the result set in Management Studio or the behavior of the data elsewhere.

Line break behavior also exists if I select one of these string records into notepad.

I/O Methods tried:

-copy and paste from SQL Server Management Studio into excel

-right-clicking in Management Studio and Saving Results as CSV

-pulling the data into python using pydobc and writing to a csv.

-pulling the data into python, using pandas to write a csv (with a variety of separators) or xslx.

Query Methods tried:

-SELECT REPLACE(String, X, '') where X is '\t' or '\n' or '\r' or ' ' or ':' et cetera.

Does anyone have any thoughts as to what this character might be, or how I might try to find it? Thanks kindly.

Upvotes: 0

Views: 142

Answers (1)

Dave.Gugg
Dave.Gugg

Reputation: 6771

Often this is char(10) line feed and/or char(13) carriage return:

SELECT REPLACE(REPLACE(string,char(10),''),char(13),'')

Upvotes: 2

Related Questions