How can I display an ntext field as a long string in a view?

Question

We have one of our systems that is using SQL Views to make reports. This are exported into CSV.

There is a table I'm working on that one of the fields is ntext, in this field we store some HTML code. It's an email log we send from the online system. Since we export to CSV, this field having break lines is giving me some trouble to display the report correctly. So I guess I need it to be displayed on the view as a long string.

Is there a way to do this?

We are using SQL Server 2005.

Thanks Federico

Answer 1

Best way. Since you should never use text fields, you should alter your column to nvarchar(max).

alter table yourtable ALTER COLUMN yourcolumn nvarchar(max)

Alternative

CAST(yourcolumn as NVARCHAR(MAX))

Answer 2

select replace(text, "\n\r", " ") from yourTable

the ntext should be converted to varchar implicitly.