Início / dba / Perguntas / 300198
Accepted
John K. N.
Asked: 2021-09-27 23:21:26 +0800 CST

Solução alternativa para REPLACE() na coluna ntext

  • 772

Visão geral

Eu tenho um banco de dados de fotos que cataloga informações de fotos tiradas com câmeras. O banco de dados contém uma tabela picturescom 256 colunas que contêm informações sobre a foto que foi tirada.

Uma coluna Commentsé formatada como ntexte contém CR/LFs.
Existem outras 21 colunas que foram configuradas como ntext.

Eu extraio os dados do banco de dados em um arquivo simples usando a Tasks | Export Data...função encontrada no SSMS. Os dados exportados são então transferidos por um parceiro externo para um novo sistema que será usado em um futuro próximo. O arquivo de exportação (CSV) contém cerca de 256 colunas com 21 colunas que podem conter CR/LFs.

Problema

O Commentscampo/coluna contém vários CR/LFs (SQL: CHAR(13), CHAR(10)) que estão impactando a análise dos dados.

Tentei usar o REPLACE(expression, value, value)para procurar o CR/LF e substituí-lo @@e estava pensando em implementar isso durante a exportação usando Export Datano SSMS.

No entanto, a REPLACE()função retorna um

Msg 8116, Level 16, State 1, Line 4 O
tipo de dados do argumento ntext é inválido para o argumento 1 da função de substituição.

...quando executo algo como:

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

Dados de amostra do comentário da coluna

Emitindo a seguinte declaração:

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

...recuperará o seguinte registro de amostra 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
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          .

Sim, há uma linha vazia. O ponto final foi inserido por mim para exibir o comprimento do texto.

Expressões regulares

Exportei os dados e executei várias expressões regulares para omitir o CR/LF dos dados. Como a coluna Commentestá no meio dos dados, tive que tentar várias strings regex:

Strings de pesquisa

([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']+)

Substituir

\1@@\3

Esta solução não foi muito eficaz nem eficiente, pois levou várias execuções e horas para arrumar o arquivo CSV.

Pergunta

Como posso substituir o CR/LF em uma ntextcoluna @@durante uma exportação para um arquivo simples CSV? Existe uma opção diferente da REPLACEque eu poderia usar?

Limitações/Observações

  • O Integration Services não foi instalado, portanto, não é uma opção.
  • A string ntext pode conter ", 'e “, ”, e ‘, ’claro, o opcional «e ».
  • Analisei os dados na ntextcoluna Commentusando a instrução SELECT MAX(DATALENGTH(Comment)) FROM Pictures;e recebi o feedback de que um registro contém 5.562 caracteres.

Eu criei um db<>fiddle com uma definição básica da tabela e os dados de exemplo junto com a REPLACE()declaração com falha.

sql-server sql-server-2014

3 respostas

  1. Best Answer

    As funções de manipulação de strings do SQL Server são um pouco inconsistentes ao lidar com strings longas, mas se você tomar cuidado para garantir que a entrada a ser pesquisada seja NVARCHAR(MAX), a REPLACE()função funcionará em dados com mais de 8.000 bytes (4.000 caracteres para tipos 'N'). Isso significa que você pode manipular NTEXTvalores longos lançando-os para NVARCHAR(MAX) e vice-versa, sem que eles sejam truncados, assim:

    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

    Isso não será particularmente eficiente, especialmente se você tiver valores realmente longos nessas NTEXTcolunas.

    Então, para o seu exemplo db-fiddle:

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

    Observe que o violino substituiu seu EOL de byte duplo por apenas CHAR(10). Além disso, você pode precisar converter para NTEXT(como feito em meus exemplos anteriores) se o aplicativo de recebimento esperar isso, por exemplo, o SSIS certamente reclamará se receber um valor de cadeia longa normal ao esperar um tipo de blob herdado.

    • 8

  2. Caso você possa usar funções CLR, você pode usar esta:

    [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()));
}

    Usando seus dados de amostra, este é o resultado:

    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@@

    Então eu tentei com um texto maior que 4000 caracteres:

    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);

    E funcionou também:

    AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA@@BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB@@CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC@@

    Não consigo usar seu violino porque não consigo adicionar uma função CLR a ele.

    • 3

  3. A pergunta está marcada com SQL Server 2016. Isso significa que você tem FOR JSON . A codificação JSON escapa corretamente de caracteres especiais, incluindo feeds de linha. Se o consumidor puder aceitar JSON, você terminou. Se não for um pouco de PowerShell / Python / qualquer que seja, será capaz de substituir \r\ne @@converter para CSV, mais facilmente do que o TSQL.

    Aqui está a configuração. Copiei todos os caracteres especiais da pergunta e inseri um avanço de linha.

    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;

    Que produz (em SSMS, Resultados para Texto)

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

(2 rows affected)

    Assim, vemos o avanço de linha representado corretamente na saída. Os mesmos dados que JSON são

    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)

    Isso pode ser enviado para um arquivo usando bcp

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

relate perguntas