Alternate Solution For REPLACE() In ntext Column

Question

Overview

I have a picture database that catalogue information of pictures taken with cameras. The database contains a table pictures which has 256 columns which contain information about the picture that has been taken.

One column Comments is formatted as ntext and contains CR/LFs.
There are another 21 columns which have been configured as ntext.

I extract the data from the database into a flat file using the Tasks | Export Data... function found in SSMS. The exported data is then transferred by an external partner to a new system which will be used in the near future. The export file (CSV) contains some 256 columns with 21 columns that could possibly contain CR/LFs.

Problem

The Comments field/column contains numerous CR/LFs (SQL: CHAR(13), CHAR(10)) which is impacting the analysis of the data.

I tried using the REPLACE(expression, value, value) to search for the CR/LF and replace it with @@ and was thinking of implementing this during the export using Export Data in SSMS.

However, the REPLACE() function returns a

Msg 8116, Level 16, State 1, Line 4
Argument data type ntext is invalid for argument 1 of replace function.

...when I execute something like:

SELECT 'Start *******************', REPLACE(Comment,'
','@@'), ID, '********************End' FROM dbo.pictures
WHERE Comment LIKE '%
%';

Sample Data From Column Comment

Issuing the following statement:

SELECT Comment FROM dbo.Pictures
WHERE Comment like '%
%';

...will retrieve the following sample Comment record:

Zwei Fotos von Arenenberg auf einer Seite einer englischen Zeitschrift.
Seite 148 der Zeitung "The Graphic" vom 4. August 1906 = News from Abroad.
"The last stage of all": the retreat for aged actors opened last week near Meaux, in France
1. General view of the home
2. M. Coquelin reciting in the open-air theater

The château of Arenenberg which has been presented by the Empress Eugénie to the canton of Thurgovie
3. View from the chateau [Arenenberg] over Lake Constance
4. The château of Arenenberg
The Empress Eugénie has presented to the Swiss Canton of Thurgovie the historical château of Arenenberg, where Napoleon III. passed several years of his youth. Queen Hortense, on the fall of the first Empire, fled to Switzerland, and in 1817 purchased the castle, which is delightfully situated on the shore of Lake / Constance. The gift includes a priceless collection of paintings, manuscripts, books, old furniture, and tapestries, among the mos important souvenirs being the camp bed of Napoleon III., and the carriage in which he left Sedan after his defeat. When the alterations are complete the château will be opened to the public.

5. The maiden voyage of the new Santos-Dumont flying machine
6. The room in Viborg where the dissolved Duma met
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          .

Yes, there is an empty line. The full stop was inserted by myself to display the length of the text.

Regular Expressions

I exported the data and then ran various Regular Expressions to omit the CR/LF from the data. Because the column Comment is in the middle of the data, I had to try various regex strings:

Search Strings

([a-zA-Z0-9/,.@():;\s]+)(\r\n)([a-zA-Z0-9/,.@():;"\s]+)
([a-zA-Z0-9/,.@():;\s]+)(\r\n\r\n)([a-zA-Z0-9/,.@():;"\s]+)
([a-zA-Z0-9/,.@():;\s]+)(\r\n\r\n\r\n)([a-zA-Z0-9/,.@():;"\s]+)
([a-zA-Z0-9/,.@():;\s]+)(\r\n\r\n\r\n\r\n)([a-zA-Z0-9/,.@():;"\s]+)
([\w/,.@():;\s']+)(\r\n)([\w/,.@():;"\s]+)
(;")(\r\n)(";)
(;")(\r\n)([\w/,.@():;\s']+)
(\w")(\r\n)([\w/,.@():;\s']+)

Replace

\1@@\3

This solution wasn't very efficacious nor efficient as it took various runs and hours to tidy up the CSV file.

Question

How can I replace the CR/LF in a ntext column with @@ during an export to a CSV flat file? Is there a different option than REPLACE that I could use?

Limitations/Observations

• Integrations Services has not been installed so that is not an option.
• The ntext string can contain ", ' and “, ”, ‘, ’ and of course the optional « and ».
• I have analysed the data in the ntext column Comment using the statement SELECT MAX(DATALENGTH(Comment)) FROM Pictures; and received the feedback that one record contains 5'562 characters.

I have created a db<>fiddle with a basic definition of the table and the sample data together with the failed REPLACE() statement.

Answer 1

SQL Server's string handling functions are a bit inconsistent when dealing with long strings, but if you take care to make sure input to be searched is NVARCHAR(MAX) then the REPLACE() function will work over data longer than 8,000 bytes (4,000 characters for 'N'-types). This means that you can manipulate long NTEXT values by casting them to NVARCHAR(MAX) and back again, without them getting truncated, like so:

INSERT @t(tt) 
    SELECT CAST(
              REPLICATE(CAST(N'x' AS NVARCHAR(MAX)), 16000) -- Note: for replicate to work as expected make sure its input is explicitly a long type
            + N'☠'
        AS NTEXT)
SELECT DATALENGTH(tt) FROM @t -- 32,002 data length so we have more than the limit of non-MAX character types
-- Now test working with the long value:
SELECT DATALENGTH(CAST(REPLACE(CAST(tt AS NVARCHAR(MAX)),'x','y') AS NTEXT)) FROM @t 
-- still 32,002, has not been truncated
SELECT DATALENGTH(CAST(REPLACE(CAST(tt AS NVARCHAR(MAX)),'x','yz') AS NTEXT)) FROM @t 
-- 64,002
SELECT RIGHT(CAST(CAST(REPLACE(CAST(tt AS NVARCHAR(MAX)),'x','yz') AS NTEXT) AS NVARCHAR(MAX)), 10) FROM @t 
-- paranoia check, we've not lost the non-ASCII character at the end through truncation or conversion

This won't be particularly efficient though, especially if you have truly long values in those NTEXT columns.

---

So for your db-fiddle example:

select REPLACE(CAST(Comment AS NVARCHAR(MAX)), CHAR(10), '@@') from Pictures;

Note that the fiddle has replaced your double-byte EOL with just CHAR(10). Also you may need to cast back to NTEXT (as done in my earlier examples) if the receiving application expects that, for example SSIS will certainly complain if it received a normal long string value when expecting a legacy blob type.

Answer 2

In case you could use CLR functions, you can use this one:

[Microsoft.SqlServer.Server.SqlFunction]
[return: SqlFacet(MaxSize = -1)]
public static SqlString fnReplace(SqlString value, SqlString oldValue, SqlString newValue)
{
    return new SqlString(((string)value).Replace(oldValue.ToString(), newValue.ToString()));
}

Using your sample data this is the result:

DECLARE @crlf NVARCHAR(2) = NCHAR(13) + NCHAR(10);
SELECT dbo.fnReplace(Comment, @crlf, N'@@') FROM Pictures;

Zwei Fotos von Arenenberg auf einer Seite einer englischen Zeitschrift.@@Seite 148 der Zeitung "The Graphic" vom 4. August 1906 = News from Abroad.@@"The last stage of all": the retreat for aged actors opened last week near Meaux, in France@@1. General view of the home@@2. M. Coquelin reciting in the open-air theater@@@@The château of Arenenberg which has been presented by the Empress Eugénie to the canton of Thurgovie@@3. View from the chateau [Arenenberg] over Lake Constance@@4. The château of Arenenberg@@The Empress Eugénie has presented to the Swiss Canton of Thurgovie the historical château of Arenenberg, where Napoleon III. passed several years of his youth. Queen Hortense, on the fall of the first Empire, fled to Switzerland, and in 1817 purchased the castle, which is delightfully situated on the shore of Lake / Constance. The gift includes a priceless collection of paintings, manuscripts, books, old furniture, and tapestries, among the mos important souvenirs being the camp bed of Napoleon III., and the carriage in which he left Sedan after his defeat. When the alterations are complete the château will be opened to the public.@@@@5. The maiden voyage of the new Santos-Dumont flying machine@@6. The room in Viborg where the dissolved Duma met@@                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          

Then I've tried with a text larger than 4000 characters:

DECLARE @myText NVARCHAR(MAX);
SET @myText  = REPLICATE(N'A', 2000) + @crlf;
set @myText += REPLICATE(N'B', 2000) + @crlf;
SET @myText += REPLICATE(N'C', 1000) + @crlf;

INSERT INTO Pictures VALUES (2, @myText);

And it worked too:

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA@@BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB@@CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC@@

I cannot use your fiddle due I can't add a CLR function to it.

Answer 3

The question is tagged with SQL Server 2016. This means you have FOR JSON. JSON encoding properly escapes special characters, including linefeeds. If the consumer can accept JSON you're done. If not a bit of PowerShell / Python / whatever will be able to replace \r\n with @@, and convert to CSV, more easily than TSQL will.

Here's the setup. I've copied all the special characters from the question and inserted a linefeed.

drop table if exists dbo.t;
create table dbo.t(i int, nt ntext);

insert dbo.t(i, nt) select 1, N'z';
insert dbo.t(i, nt) select 2, N' ", '' and “, ”, ‘, ’ and of course'+ CHAR(13) + CHAR(10) + ' the optional « and ».';

select * from dbo.t;

Which produces (in SSMS, Results to Text)

i           nt
----------- ----------------------------------
1           z
2            ", ' and “, ”, ‘, ’ and of course
 the optional « and ».

(2 rows affected)

So we see the linefeed properly represented in the output. The same data as JSON is

select * from dbo.t for json auto;

JSON_F52E2B61-18A1-11d1-B105-00805F49916B
-----------------------------------------------------------------------------------------------
[{"i":1,"nt":"z"},{"i":2,"nt":" \", ' and “, ”, ‘, ’ and of course\r\n the optional « and »."}]

(2 rows affected)

This can be sent to a file using bcp

bcp "select * from dbo.t for json auto" queryout c:\Development\dumpfile.json -S MyServer\TheInstance -T -d SomeDatabase -c