ntext 列中 REPLACE() 的替代解決方案
概述
我有一個圖片數據庫，其中包含用相機拍攝的圖片的目錄資訊。該數據庫包含一個表，該表
pictures有 256 列，其中包含有關已拍攝照片的資訊。一列
Comments被格式化為ntext並包含 CR/LF。還有另外 21 列已配置為
ntext.
Tasks | Export Data...我使用SSMS 中的函式將數據庫中的數據提取到一個平面文件中。導出的數據隨後由外部合作夥伴傳輸到將在不久的將來使用的新系統。導出文件 (CSV) 包含大約 256 列，其中 21 列可能包含 CR/LF。問題
Comments欄位/列包含許多影響數據分析的CR /LF (SQL:CHAR(13), )。CHAR(10)我嘗試使用
REPLACE(expression, value, value)來搜尋 CR/LF 並將其替換為並正在考慮在使用SSMS@@的導出期間實現此功能。Export Data但是，該
REPLACE()函式返回一個消息 8116，級別 16，狀態 1，第 4 行
參數數據類型 ntext 對於替換函式的參數 1 無效。
…當我執行以下操作時：
SELECT 'Start *******************', REPLACE(Comment,' ','@@'), ID, '********************End' FROM dbo.pictures WHERE Comment LIKE '% %';來自列註釋的範例數據
發表以下聲明：
SELECT Comment FROM dbo.Pictures WHERE Comment like '% %';…將檢索以下範例
Comment記錄：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 .是的，有一個空行。我自己插入了句點以顯示文本的長度。
常用表達
我導出數據，然後執行各種正則表達式以從數據中省略 CR/LF。因為該列
Comment位於數據中間，所以我不得不嘗試各種正則表達式字元串：搜尋字元串
([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']+)代替
\1@@\3這個解決方案不是很有效也不是很有效，因為它需要多次執行和數小時來整理 CSV 文件。
問題
如何在導出到 CSV 平面文件期間替換
ntext列中的 CR/LF？@@有比REPLACE我可以使用的不同的選擇嗎？限制/觀察
- 尚未安裝集成服務，因此這不是一個選項。
- ntext 字元串可以包含
",'and“,”,‘，’當然還有可選的«and»。- 我已經使用語句分析了該
ntext列中的數據，並收到了一條記錄包含5'562個字元的回饋。Comment``SELECT MAX(DATALENGTH(Comment)) FROM Pictures;我創建了一個db<>fiddle，其中包含表的基本定義和範例數據以及失敗的
REPLACE()語句。
SQL Server 的字元串處理函式在處理長字元串時有點不一致，但如果您注意確保要搜尋的輸入是，
NVARCHAR(MAX)那麼該REPLACE()函式將處理超過 8,000 個字節（“N”類型為 4,000 個字元）的數據。這意味著您可以NTEXT通過將 long 值轉換為 NVARCHAR(MAX) 並再次返回來操作它們，而不會被截斷，如下所示：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但這不會特別有效，特別是如果您在這些
NTEXT列中有真正的長值。因此，對於您的 db-fiddle 範例：
select REPLACE(CAST(Comment AS NVARCHAR(MAX)), CHAR(10), '@@') from Pictures;請注意，小提琴已將您的雙字節 EOL 替換為
CHAR(10). 此外，如果接收應用程序期望這樣，您可能需要轉換回NTEXT（如我之前的範例中所做的那樣），例如，如果 SSIS 在期望舊 blob 類型時收到正常的長字元串值，那麼它肯定會抱怨。