Replace substring with carriage return

Hi sow56091,
Did you already try CHR(10)?

Cheers!

Stefan

Hi,
The title of your question says 'carriage return', which is a Chr(13) as per your attempt, but your explanation says 'newline character', which is the Chr(10) mentioned by Stefan.  Which is it, or is it both?  And what database is your data currently in?

Ken

Try CHAR(10) for linefeed character or CHAR(13) for carriage return

The database is MySQL and I have tried char(10) newline, chr(10), char(13),chr(13) but I have the same problem.

In fact, I have blobs and they need to have carriage returns or line feeds (whichever is used on an HTML textarea).

select id from table where field like '%'+CHAR(13)+'%'  (I also tried CHAR(10))
          I tried this but it returns fields which I should not inlude carriage returns (I imported an excel file using NaviCat, but does it end each line with a carriage return or line feed?)

Sean

Are you sure you can use the replace function at all with BLOBs?

It seems when I run this:
          UPDATE

Health Canada
 Canadian Institutes of Health Research (CIHR) and The Royal College of Physicians and Surgeons of Canada (RCPSC)

You may need a char(13) + char(10) for a textarea

Ooops, sorry for that previous post. Here's the full text:

It seems when I run this:
          UPDATE table SET field=replace(field,"///",CHAR(13));
I get text like this:
   BLAHBLAH [] BLAHBLAHBLAHBLAH  (where [] means a Windows-represented empty box thingy)

I tried copying that text from navicat's window to this discussion and indeed I saw a carriage return! I suspect (does anyone agree?) that my import (which returned all non-null blobs as containing a CHAR(13)) terminated all the strings which were imported with a CHAR(13).

Example (notice the newline after Health Canada?)

Health Canada
Canadian Institutes of Health Research (CIHR) and The Royal College of Physicians and Surgeons of Canada (RCPSC)

Sean

I just checked another few things and it seems that in fact, all my strings were terminated with a newline, so I tell you what. For those that responded, I will give all 500 points to the first person who can explain the difference between carriage return, line feed, and newline. Thanks in advance,

Sean

Although Wkalata hadn't participated earlier i split the points with rherguth who did and responded first. Thanks to everyone who commented.

Sean

SELECT REPLACE('///','\n ')
       AS replace
  FROM table





http://www.quest-pipelines.com/newsletter-v5/0204_A.htm