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

Posted on November 28, 2012

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

Filed Under iReport | 1 Comment

Leave a Comment

If you would like to make a comment, please fill out the form below.

You must be logged in to post a comment.

© Copyright Jeff's Blog • Powered by Wordpress • Using Detour theme created by Brian Gardner.