Como gerir quebras de linha em colunas e variáveis de texto no SQL Server?
Tutorial para inserir ou remover quebras de linha no texto do SQL Server armazenado nas colunas NVARCHAR ou nas variáveis T-SQL. Por exemplo, se exportar a string para o Excel, a sua linha será cortada. Na verdade, com o SSMS, se selecionar dados contendo retorno de carro ou novas linhas, o ecrã exibirá o conteúdo completo da linha.
Depois de copiar e colar no Editor do SQL Server ou no seu editor de texto favorito, como o Notepad++ por exemplo, o texto pode conter algumas quebras de linha. Ou após a integração de arquivos CSV usando SSIS, dependendo do tamanho da fonte, pode ser complicado organizar tudo e identificar as linhas de erro em detalhe.
Table of Contents
1. Problema com quebras de linha e casos de uso comuns
Considere um cenário em que uma quantidade significativa de dados é copiada dos resultados da consulta SSMS para uma folha do Excel, incluindo quebras de linha. Neste caso, os dados exibidos no Excel podem parecer desalinhados e diferentes dos resultados da consulta original, causando resultados imprecisos devido a colunas deslocadas.
A estrutura de tabela desejada é comprometida porque cada quebra de linha gera uma nova linha, resultando em um número inconsistente de colunas e linhas no arquivo do Excel. As quebras de linha podem ser particularmente problemáticas em arquivos extensos, pois são difíceis de localizar e resolver. Para resolver esse problema, recomenda-se eliminar as quebras de linha diretamente da consulta do SQL Server.
2. A composição de uma nova linha é retorno de carro e avanço de linha
Em arquivos de texto, o caractere de controle conhecido como retorno de carro (CR) instrui o cursor a transitar para o início da linha seguinte. A sequência CR + LF serve como um marcador que sinaliza a conclusão de uma linha lógica e o início de outra dentro desses arquivos. Como tal, esta sequência está presente no final de cada linha lógica em um arquivo de texto.
A combinação de retorno de carro e avanço de linha constitui a forma mais fundamental de um marcador de fim de linha, referido como retorno de carro literal ou duro. A sequência CR + LF pode representar uma nova linha de texto ou indicar que o próximo caractere não deve ser exibido na linha existente. Esta sequência é empregada para diferenciar entre linhas de texto dentro de um arquivo de dados.
3. Inserir quebras de linha em uma tabela de amostra
Primeiro, vamos preparar uma tabela com alguns dados de amostra que contenham quebras de linha. Estamos dividindo aqui a coluna de endereço em três linhas diferentes. Execute primeiro a consulta abaixo. Realiza 4 operações diferentes no SQL:
- Verifique se a tabela Customers existe e, em seguida, exclua-a.
- Crie a tabela de clientes.
- Insira uma linha única de dados, com várias quebras de linha na coluna Endereço.
- Seleciona todos os dados da tabela.
-- 1. Se a tabela existir, exclua-a
IF EXISTS(
SELECT 1 FROM sys.objects
WHERE object_id = object_id(N'dbo.CUSTOMERS')
AND type in (N'U') )
BEGIN DROP TABLE [dbo].[CUSTOMERS]
END;
GO
-- 2. criar a tabela dos CLIENTES : CUSTOMERS
CREATE TABLE [dbo].[CUSTOMERS] (
[CustomerID] INT IDENTITY(1,1),
[FirstName] NVARCHAR(20),
[LastName] NVARCHAR(20),
[AddressText] NVARCHAR(100),
[CountryCode] NVARCHAR(3),
[CountryName] NVARCHAR(50)
);
GO
-- 3. Insira o primeiro valor de vendas para cada mês
INSERT INTO dbo.CUSTOMERS ( FirstName, LastName, AddressText, CountryCode, CountryName )
VALUES ( N'SMITH',N'John',
N'123,
Mount Gatineau Avenue,
Ottawa',
N'CA',N'CANADA');
GO
-- 4. seleccionar para verificar os dados
SELECT * FROM dbo.CUSTOMERS;
GO
Depois, se copiar e colar a coluna AddressText do resultado do SQL Server, você notará que o endereço contém duas quebras de linha, conforme no comando INSERT.
123, Mount Gatineau Avenue, Ottawa
Para ir mais além, esta é a documentação da função REPLACE integrada no SQL Server.
4. Remover retornos de carro do SQL Server usando a função replace
Na verdade, use a função replace simples e os códigos CHAR(13) e CHAR(10). Esses dois códigos específicos representam os caracteres de quebra de linha. Em seguida, substitua-os por espaço ou vírgula. Por exemplo, para limpar a coluna Endereço das novas quebras de linha.
-- Check the results
SELECT CustomerID, FirstName, LastName, AddressText
FROM dbo.CUSTOMERS;
-- Using the REPLACE function to remove the line breaks
SELECT CustomerID, FirstName, LastName,
REPLACE(AddressText,CHAR(13)+CHAR(10),' ')
FROM dbo.CUSTOMERS;
Desta vez, o resultado da cópia e colagem é este. Ele mostra claramente o endereço do cliente sem mais quebras de linha, agora as três partes do endereço estão na mesma linha.
123, Mount Gatineau Avenue, Ottawa
Inserir ou remover quebras de linha do SQL Server usando funções REPLACE e CHAR.
5. Inserir quebras de linha em uma string usando a função CHAR do T-SQL
Agora, de maneira inversa, para adicionar uma quebra de linha a uma coluna ou qualquer variável de texto em uma consulta T-SQL, basta adicionar os dois caracteres especiais como abaixo. Na verdade, para executar o oposto, use o mesmo método, mas inverta os argumentos na função, por exemplo, substitua um espaço ou um caractere específico pelo casal:
- CHAR(13)
- CHAR(10)
-- Consulta SQL para adicionar um retorno de carruagem e construir o endereço completo SELECT [CustomerID] ,[FirstName] ,[LastName] ,[AddressText] + CHAR(13) + CHAR(10) +[CountryCode] + CHAR(13) + CHAR(10) +[CountryName] AS [FullAdress] FROM dbo.CUSTOMERS;
Para ver os retornos de carro, ative a opção Resultados para Texto no SSMS.
Conclusão sobre gerenciamento de retornos de carro no T-SQL
Em conclusão, os retornos de carro não são um grande problema no SQL Server, desde que sejam gerenciados adequadamente. Eles podem ser controlados, garantindo que os CHAR(10) e CHAR(13) do SQL Server sejam gerenciados corretamente. Os controles de entrada ajudam muito a reduzir o número de caracteres inválidos inseridos em um banco de dados.
Isso é útil principalmente na formatação de dados, ao transferi-los entre aplicativos como SSMS e Excel, por exemplo. Este tutorial forneceu a informação necessária para inserir ou remover quebras de linha em colunas de texto e variáveis do SQL Server. Ao usar as funções REPLACE e CHAR, você sabe como trabalhar com retornos de carro e avanços de linha. Esses scripts podem ajudá-lo a manter a integridade dos seus dados e evitar erros causados por colunas desalinhadas ou contagens de linhas inconsistentes.
Perguntas sobre o caractere de nova linha no MS SQL
Uma quebra de linha ou retorno de transporte ocorre quando uma linha é dividida em duas ou mais partes. O resto da linha actual aparece na linha seguinte.
Um dos melhores programas para edição de ficheiros é o Notepad++. Ele reconhece muitas outras linguagens, tais como HTML, CSS, PHP, XML ou SQL.
Ambos os caracteres especiais char(13) e char(10) estão relacionados às novas linhas em arquivos de texto. Char(13) é o retorno de carro e char(10) é o símbolo de avanço de linha.
Para remover o retorno do carro directamente de uma coluna SQL, usar uma função T-SQL. Basta inseri-la numa consulta de actualização com a cláusula UPDATE e substituí-la por um espaço ou uma string vazia.
