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:
Line One. Line Two. Line Three.
Popular search terms:
I want to concatenate addr1 addr2 addr3 columns using as400 sql and print as follows
addr1,
addr2,
addr3.
I want to conatenate 3 address columns as
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.
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
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!