Hidden Characters 1

[IanWaterman]

I have a memo field which I have converted to Varchar and added RTRIM and REPLACE. However, there are some mysterious characters which cause two line feeds or Carriage returns.

I know the replace below is over kill but I was getting desparate, I used 2 spaces so as to preserve spacing of other text. Even if I use only 1 space problem remains. If I leave as a memo field problem remains.

Replace(Rtrim(cast("vw_Proposed_Endorsements"."Comments" as varchar(4000))), ' ', '') as "Comments"

How do I display what they are so that I can get rid of them, they are ruining display of report via Reporting Services. Oddly when the same field is displayed in Crystal Reports as a memo field the additional two rows do not appear.

I have pasted output field into Word and Notepad plus and switched on show formatting and the are definitely not CR or LF, they simply appear as spaces.

Ian

 

[MarkSweetland]

It does appear that the offending charaters are LF and CR. Have you tried specifically to remove them?

[Alias]=rtrim(REPLACE(convert(varchar(4000), [columnName]), char(13, ' '))

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[IanWaterman]

Mark

You were dead right I had tried to remove LF and CR in reporting services as i did not know syntax in SQL, it was in fact a LineFeed

rtrim(REPLACE(convert(varchar(4000), "vw_Proposed_Endorsements"."Comments"), char(10), ' ')) as "Comments"

This cured problem.

Thank you very much

Ian