Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2658
  • Last Modified:

Replace substring with carriage return

Hello experts,

I exported an old database and need to replace /// in a blob with a newline character by using a sql query. This is what i have so far:

update Table set Field=replace(Field,"///",?????????)
     the ????? part is what I don't know. I tried chr(13) but it didn't work.

Any ideas?
0
sow56091
Asked:
sow56091
  • 5
  • 2
  • 2
  • +4
2 Solutions
 
stefan73Commented:
Hi sow56091,
Did you already try CHR(10)?

Cheers!

Stefan
0
 
kenspencerCommented:
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
0
 
antonbijlCommented:
Try CHAR(10) for linefeed character or CHAR(13) for carriage return
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
sow56091Author Commented:
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
0
 
stefan73Commented:
Are you sure you can use the replace function at all with BLOBs?
0
 
sow56091Author Commented:
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)
0
 
rherguthCommented:
You may need a char(13) + char(10) for a textarea
0
 
sow56091Author Commented:
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
0
 
sow56091Author Commented:
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
0
 
WKalataCommented:
a Carriage return is CHR(13), a Line Feed is CHR(10), a newline depends on the OS.  On windows, a newline is CHR(13) followed by CHR(10), on unix-type systems, it is only a chr(10), on macs, it is either only chr(13), or only chr(10), depending on version.  No, there is no enlightened reason for all of it.
0
 
rherguthCommented:
Back in the day of impact printers a carriage return caused the printer to swing the carriage to the first character in the line.  A line feed caused it to feed the paper by one line.  A newline does the same thing as a line feed, but doesn't harken back to printers, but text editors.
0
 
sow56091Author Commented:
Although Wkalata hadn't participated earlier i split the points with rherguth who did and responded first. Thanks to everyone who commented.

Sean
0
 
ajaypappanCommented:
SELECT REPLACE('///','\n ')
       AS replace
  FROM table





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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now