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.
Comments is formatted as
ntext and contains CR/LFs.
There are another 21 columns which have been configured as
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.
Comments field/column contains numerous CR/LFs (SQL:
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.
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
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.
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:
([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']+)
This solution wasn't very efficacious nor efficient as it took various runs and hours to tidy up the CSV file.
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?
- Integrations Services has not been installed so that is not an option.
- The ntext string can contain
’and of course the optional
- I have analysed the data in the
Commentusing 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