Insert Carriage Return Line Feed to String

Here is a clean and efficient way to embed carriage returns into a string. I prefer this way instead of concatenating the entire CHAR(13)+CHAR(10) to the end of every string. This replace function also comes in handy in most instances where you need to append strings.


SET @Note = 'Line One.[crlf];Line Two[crlf]Line Three.'

SET @Note = REPLACE(@Note,'[crlf]',CHAR(13)+CHAR(10))


Line One.
Line Two.
Line Three.

Sahani 21 Dec 2011 at 12:02 am

I want to concatenate addr1 addr2 addr3 columns using as400 sql and print as follows

Sahani 21 Dec 2011 at 12:00 am

I want to conatenate 3 address columns as

Mark 07 Oct 2011 at 8:13 am

Thanks for the tip, I was having issues generating IIF files for QuickBooks and they require a CRLF, but when it came out of BCP, it only had the LF. This fixed it for me.

alistair 18 Dec 2010 at 8:50 am

Hi Derek,
Do you know how this can be adapted to use in a select / update statement on many rows in a table? I tried to adapt the above into a function, but i can’t get it to work.
I did this for example…
select REPLACE(‘line one[crlf]line two.’,'[crlf]’,CHAR(13)+CHAR(10))
Do you have any ideas?

Derek Dieter 20 Dec 2010 at 2:54 pm

Hi Alistair,

Yes, if I’m understanding you correctly, this should be the same way mentioned above.

So if you want update all rows in a table, you would do:

SELECT NewNotesColumn = REPLACE(n.NotesColumn,'[crlf]’,CHAR(13)+CHAR(10))
INTO #NewNotesTable
FROM Notes n

Good luck!

Featured Articles

Learn Index Tuning!

Index Tuning Video Training
only $49
You'll learn everything practical there is to know about index tuning with this downloadable video.
Learn more

 Site Author

  • Thanks for visiting!