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.

Myungok 27 Nov 2015 at 9:48 pm

It sure is a lot to take in, isn’t it? Here’s a little soihnemtg from Tom Kyte to lighten the load of every DBA, an SQL Joke: “A SQL query walks into a bar . . . ” It’s Tom’s punchline—click through!

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.

Christiane 27 Nov 2015 at 8:48 pm

– SQL is generally spkeon and read to mean “sequel” – not an acronym S-Q-L, but read as a word sequelAn sequel Joke – not right.A sequel Joke – right.An S.Q.L. Joke – rightSo, it depends on whether you are in the “sequel” camp (like I am) or the S.Q.L. camp…A SQL Joke is the way I’ll be sticking with.

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” . . .

Emanuel 27 Nov 2015 at 8:32 pm

It’s pure evil I tell you.You show someone some plsql and they learn a lltite about functions and stored procedures etc.Then they want to start showing people they know some plsql and they start putting in function calls into sql.Well we know ( and your example clearly shows ) how bad it is to try to use plsql when you can do it only in sql … But it happens again and again and … You would think peo0ple are actually trying to do things in oracle instead of reading all your books 3 or 4 times!

John 27 Nov 2015 at 7:54 pm

I LOVE LOVE LOVE the very first one, but the one with the pink top almost made me cry. That’s how befuitaul you looked in it! Do you know what you will be doing with your hair?

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!

Penakluq 27 Nov 2015 at 8:11 pm

No, the thermometer is crap. Lasted a few miunets then fogged up with moisture and is WAY off actual temp. What did I expect? Got myself a decent one and now am enjoying the finest in infrared and smoke cooking. Did a pork shoulder last night and it was WOW!

Featured Articles

 Site Author

  • Thanks for visiting!