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.

Rejane 29 Aug 2015 at 7:02 am

Hi Tom. Sorry to bother you with this but it seems I need a ggoole account to post in Steve’s and I couldn’t get myself a Quest account yet,so just to know:does this new product run on Linux?

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

Cristiane 29 Aug 2015 at 11:27 am

I think the pink top dress gave you the glow pop color to your skin and looked great on you more than the oterhs but all of the oterhs look good too.

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.

Ruben 29 Aug 2015 at 6:37 am

Wow – Tom and Steven meeting for the first time. Sounds like one of those cool moentms, like when Lennon and McCartney first met or when Churchill and FDR first sat down together. Talk about a “meeting of the minds” . . .

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

 Site Author

  • Thanks for visiting!