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.
[cc lang=”sql”]
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
[/cc]
Output:
[code]
Line One.
Line Two.
Line Three.
[/code]

8 comments
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
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.

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!

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

  • Thanks for visiting!
css.php