Insert Carriage Return Line Feed to String
-
Posted on June 1, 2009 by Derek Dieter
-
0
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]
- Comments (RSS)
- Trackback
- Permalink
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?
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.
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” . . .
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!
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!
