iReport – Removing Carriage Returns and Newlines With the replace() Funtion

iReport — Jeff Eske on November 28, 2012 at 4:11 pm

For some time, I’ve been trying to save information out of an MS SQL database that’s going away.  I’m wanting the data in a .csv file and the problem that I’ve been having is that a couple of the columns that I want contain data that includes carriage returns and newline characters within the column.  When the data is saved out, those returns look like the end of a record, essentially breaking every record containing them.  This also makes the data virtually useless for importing into other databases, since the records are broken, column counts end up off, etc.

What ended up solving the problem for me was the replace() function within iReport.  Since I was only concerned with the content and not necessarily the formatting, I used replace() to take out the returns and replace them with a single space.  One additional thing that you’ll notice is that the data is on an old version of MS SQL, and after considerable trial-and-error, I found that I had to actually use cast() function as well.

The complete syntax that I used:

[code]

CAST(REPLACE(CAST(Column_Name as NVarchar(4000)),CHAR(13)+char(10),’ ‘) AS NText) as Some_Name

[/code]

Another issue that I thought I had run into was a problem with some columns only having a newline, not both.  Well, I found out that I can nest the first replace() function and use the first output as the input for the next, so that I could actually make another pass through the data to catch the individual newlines the second time around.  THAT setup starts to look really messy, due needing the cast() function as well…

The syntax for nesting cast() and replace() functions:

[code]

CAST(REPLACE(CAST(CAST(REPLACE(CAST(Column_Name as NVarchar(4000)),CHAR(13)+char(10),’ ‘) AS NText) as NVarchar(4000)),char(10),’ ‘) AS NText) as Some_Name

[/code]

This solved part of my problem.  Within my iReport report, the columns looked right and complete.  At that point, I was ready to save everything out as a .csv file.  That ended up being a minor problem also.

Jeff

1 Comment

  1. […] I have been trying to save some old data off of a database, before the database server is decommissioned.  I was hoping to save the data out in a .csv file, but the first problem that I ran into was that some of the columns that I wanted to keep contained carriage returns and newline characters.  When the data was saved out to .csv, the carriage returns and newlines would appear as the end of a record, causing said records to break.  Eventually, I was able to solve that issue. […]

    Pingback by Jeff's Musings » iReport – Removing Pagination — November 28, 2012 @ 4:35 pm

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License. | Jeff's Blog