?
Solved

Replace substring with carriage return

Posted on 2005-03-29
13
Medium Priority
?
2,653 Views
Last Modified: 2008-01-09
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
Comment
Question by:sow56091
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +4
13 Comments
 
LVL 12

Expert Comment

by:stefan73
ID: 13655928
Hi sow56091,
Did you already try CHR(10)?

Cheers!

Stefan
0
 
LVL 3

Expert Comment

by:kenspencer
ID: 13656165
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
 
LVL 4

Expert Comment

by:antonbijl
ID: 13656223
Try CHAR(10) for linefeed character or CHAR(13) for carriage return
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:sow56091
ID: 13656306
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
 
LVL 12

Expert Comment

by:stefan73
ID: 13656332
Are you sure you can use the replace function at all with BLOBs?
0
 
LVL 3

Author Comment

by:sow56091
ID: 13656424
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
 
LVL 9

Expert Comment

by:rherguth
ID: 13656444
You may need a char(13) + char(10) for a textarea
0
 
LVL 3

Author Comment

by:sow56091
ID: 13656492
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
 
LVL 3

Author Comment

by:sow56091
ID: 13656600
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
 
LVL 2

Assisted Solution

by:WKalata
WKalata earned 1000 total points
ID: 13657288
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
 
LVL 9

Accepted Solution

by:
rherguth earned 1000 total points
ID: 13658280
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
 
LVL 3

Author Comment

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

Sean
0
 
LVL 3

Expert Comment

by:ajaypappan
ID: 13662263
SELECT REPLACE('///','\n ')
       AS replace
  FROM table





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

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question