[thelist] replacing line breaks in sql 2000

[Anthony Baratta]

Brian Cummiskey wrote:
> Hi guys,
> 
> I have a problem with a feed i'm working on.   The flat-file export is 
> giving me some headaches due to linebreaks.
> 
> viewing the file in notepad, i can see the hard break char [] square.

That might not be what you think it is. I'm not sure how you are 
exporting to a flat file, but if you have the option (or write a quicky 
script in the language of your choice) use a function like URL Encode 
that will replace those "invisible" characters with their %## equivalents.

Then you can track down what the exact characters are and hack them out 
appropriately.

> This is the update i'm using before export:
> 
> declare @NewLine char(2)
> set @NewLine=char(13)+char(10)
> 
> update @temp
>    	 set OfferDescription = Replace(OfferDescription , @NewLine,'')
> 	WHERE OfferDescription like '%' + at NewLine +'%'

Using like will force a full table scan, so you don't really need to use 
it in this context. You end up making two passes on the data, albeit the 
second pass is only on the sub-set of data you want.



--
Anthony Baratta

Begin each day by telling yourself: Today I shall be meeting with 
interference, ingratitude, insolence, disloyalty, ill-will, and selfishness.
-- Emperor Marcus Aurelius (161-80 B.C.)
    Meditations, Bk. II, Para. 1.