0

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

Federico Giust
  • 1,803
  • 4
  • 20
  • 45
  • How do you export it? bcp? osql? – gbn Nov 15 '11 at 13:34
  • `NTEXT` is deprecated in SQL Server 2005 - you should use `NVARCHAR(MAX)` instead. Added benefit: you can use the regular string functions on those columns now – marc_s Nov 15 '11 at 13:39

2 Answers2

0

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))

t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
  • I know this would be the best way. But some rows have more than 30 thousand characters, so at the moment we don't see this as an option. – Federico Giust Nov 15 '11 at 14:20
  • @Federico Giust: NVARCHAR(MAX) is 2GB bytes. The **MAX** is important – gbn Nov 15 '11 at 14:27
  • @FedericoGiust not only is it an option, it is about to become a nessesity when NTEXT is deprecated. You really should change it to nvarchar like described. – t-clausen.dk Nov 17 '11 at 09:28
0
select replace(text, "\n\r", " ") from yourTable

the ntext should be converted to varchar implicitly.

ComfortablyNumb
  • 3,391
  • 2
  • 14
  • 15
  • I've been googling around and it seems that I can't use replace on an next field. :( – Federico Giust Nov 15 '11 at 14:19
  • replace can use with char type or binary type. But given ntext will be cast to varchar automatically when calling replace, it should work. Though I don't have sql server on hand, I didn't try. You can cast the ntext to varchar anyway. – ComfortablyNumb Nov 15 '11 at 14:22
  • @Phil the OP would probably better off converting to Nvarchar since its Ntext. – Conrad Frix Nov 15 '11 at 17:39