.replace of extra line feeds

[jason]

Hello.

I just converted some data to to sql2000 into a TEXT type field. I
needed to remove all tabs from the data and now want to remove extra
lines too.
As we know, SQL2000's replace function does not work on TEXT types. So
I've written a simple asp.net program (with very high timeouts) that
allows me to replace strings in these very large Text fields. I loop
through the table and am able to replace any strings I want except I
can't seem to figure out how to replace extra blank lines with a
single line feed and carriage return.

Comparing the old data to the new data. I see I have extra hex values
between lines of

old = 0D0A
new = 0D0A0D0A

so I'm looking to replace 0D0A0D0A with 0D0A.

Here's the asp.net (VB) I have tried. None yeild the change:

dim thebody as string
thebody=thebody.replace(chr(13)+chr(10)+chr(13)+chr(10),chr(13)+chr(10))
thebody=thebody.replace(chr(13)&chr(10)&chr(13)&chr(10),chr(13)&chr(10))
thebody=thebody.replace(vbCrLf&vbCrLf,vbCrLf)

Again, the same logic does replace any other string including Tab =
chr(9)

Thanks for any help or info!

 

[Michael Evanchik]

did you try Environment.NewLine?

If you try ret=thebody.IndexOf(vbcrlf) or chr(13) chr(10) do you
get a value greater then -1 in the string?

If in worst case you can try looping throught the string substring or
miding the results together.

Mike

 

[jason]

did you try Environment.NewLine?

If you try ret=thebody.IndexOf(vbcrlf) or chr(13) chr(10) do you
get a value greater then -1 in the string

Thanks for response.

I did not, but that actually would have uncovered my problem. After
posting I did a select in sql2000 QA and found that either asp.net or
the html textbox was adding a chr(10)=Line Feed between consecutive
chr(13)s=Carriage Returns Only in the textbox, not found in the DB.
So, I was able to fix my Text type fields by preplacing
chr(13)&chr(13) to "".

I've seen a number of posts listing very long and complicated SQL2000
stored procs to performing what should be a simple text string
replace. ASP.NET seems to be a simple way around the problem!