Replacing specific text characters in a TEXT field with CRLF characters

[benedick96]

In SQL Server, when you have a TEXT field, it can store some long-text.
You can also insert a CRLF in the text so that it displays with a CRLF
on-screen and when printed.

I am using a customized program to import data into SQL Server (a
program I cannot edit).
I have source data that has three string fields that I want to
concatenate into a single TEXT field in SQL Server.

For example:
String1: Please call John
String2: at extension 55
String3: Tax ID# 12345

I want this to go into the SQL Server text field as:
Please call John + CRLF+ at extension 55 + CRLF + Tax ID# 12345

However, the data import tool I'm using won't allow this.
The best I can do is replace the CRLF with a specific text string.
In this case, I chose $LF$

So - in the SQL text field I have the following data:

Please call John$LF$at extension 55$LF$Tax ID# 12345

I am looking for some TSQL code I can run the will replace the specific
text string $LF$ with a CRLF. Anyone have an idea how to do this?
thanks!

[Aaron Bertrand [SQL Server MVP]]

Post by benedick96
I am looking for some TSQL code I can run the will replace the specific
text string $LF$ with a CRLF. Anyone have an idea how to do this?

This can get pretty complicated.

If you are using SQL Server 2005, you can change the column to VARCHAR(MAX),
and then simply UPDATE tbl SET col = REPLACE(col, '$LF$',
CHAR(13)+CHAR(10)).

If you do not have the luxury of using SQL Server 2005 and this much more
flexible data type, this should get you started (but it may take some
experimentation):

http://www.aspfaq.com/2445

Note especially the lines that need to change for the script to work against
TEXT instead of NTEXT.

A

[benedick96]

OK - well that's a lot more complicated than I thought.

Let's start with this ...
What CHARACTERS would I use to indicate a CRLF?
I've tried all kinds of values and I'm not even sure what to use for
it.
For example, I've tried ...
CHAR(13)+CHAR(10)
\x0d\x0a
\r\n
CHR(13)+CHR(10)

Any ideas on what text would represent a CRLF in SQL Server TEXT fields?

[Aaron Bertrand [SQL Server MVP]]

Post by benedick96
What CHARACTERS would I use to indicate a CRLF?
I've tried all kinds of values and I'm not even sure what to use for
it.
For example, I've tried ...
CHAR(13)+CHAR(10)
\x0d\x0a
\r\n
CHR(13)+CHR(10)
Any ideas on what text would represent a CRLF in SQL Server TEXT fields?

CHAR(13)+CHAR(10). But they cannot be WITHIN the text. E.g. these are two
very different things:

SELECT x = 'foo + CHAR(13)+CHAR(10) + bar';

SELECT x = 'foo' + CHAR(13)+CHAR(10) + 'bar';

Subtle but important difference.

A