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.

DECLARE @Note VARCHAR (200)

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

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

PRINT @Note

Output:
[code]
Line One.
Line Two.
Line Three.
[/code]

10 comments
Sahani 21 Dec 2011 at 12:02 am

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

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?
Cheers,
Alistair

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