Substitua espaços duplicados por um único espaço no T-SQL

Eu preciso garantir que um determinado campo não tenha mais de um espaço (não estou preocupado com todo o espaço em branco, apenas espaço) entre os caracteres.

assim

'single spaces only' 

precisa ser transformado em

 'single spaces only' 

O abaixo não vai funcionar

 select replace('single spaces only',' ',' ') 

como isso resultaria em

 'single spaces only' 

Eu realmente prefiro ficar com o T-SQL nativo em vez de uma solução baseada em CLR.

Pensamentos?

Ainda mais arrumado:

 select string = replace(replace(replace(' select single spaces',' ','<>'),'><',''),'<>',' ') 

Saída:

selecione espaços únicos

Isso funcionaria:

 declare @test varchar(100) set @test = 'this is a test' while charindex(' ',@test ) > 0 begin set @test = replace(@test, ' ', ' ') end select @test 

Se você sabe que não haverá mais de um certo número de espaços em uma fila, você pode aninhar a substituição:

 replace(replace(replace(replace(myText,' ',' '),' ',' '),' ',' '),' ',' ') 

4 substituições devem corrigir até 16 espaços consecutivos (16, depois 8, depois 4, depois 2, depois 1)

Se pudesse ser significativamente mais longo, você teria que fazer algo como uma function in-line:

 CREATE FUNCTION strip_spaces(@str varchar(8000)) RETURNS varchar(8000) AS BEGIN WHILE CHARINDEX(' ', @str) > 0 SET @str = REPLACE(@str, ' ', ' ') RETURN @str END 

Então faça

 SELECT dbo.strip_spaces(myText) FROM myTable 

Isso é um pouco de força bruta, mas vai funcionar

 CREATE FUNCTION stripDoubleSpaces(@prmSource varchar(max)) Returns varchar(max) AS BEGIN WHILE (PATINDEX('% %', @prmSource)>0) BEGIN SET @prmSource = replace(@prmSource ,' ',' ') END RETURN @prmSource END GO -- Unit test -- PRINT dbo.stripDoubleSpaces('single spaces only') single spaces only 
 update mytable set myfield = replace (myfield, ' ', ' ') where charindex(' ', myfield) > 0 

Substituir irá funcionar em todos os espaços duplos, não há necessidade de colocar em vários substitui. Esta é a solução baseada em conjunto.

Aqui está uma function simples que criei para limpar os espaços antes ou depois e vários espaços dentro de uma string. Ele manipula graciosamente até cerca de 108 espaços em um único trecho e quantos blocos houver na string. Você pode aumentar isso por meio de fatores de 8, adicionando linhas adicionais com pedaços maiores de espaços se for necessário. Parece ter um desempenho rápido e não causou nenhum problema, apesar de seu uso generalizado em um aplicativo grande.

 CREATE FUNCTION [dbo].[fnReplaceMultipleSpaces] (@StrVal AS VARCHAR(4000)) RETURNS VARCHAR(4000) AS BEGIN SET @StrVal = Ltrim(@StrVal) SET @StrVal = Rtrim(@StrVal) SET @StrVal = REPLACE(@StrVal, ' ', ' ') -- 16 spaces SET @StrVal = REPLACE(@StrVal, ' ', ' ') -- 8 spaces SET @StrVal = REPLACE(@StrVal, ' ', ' ') -- 4 spaces SET @StrVal = REPLACE(@StrVal, ' ', ' ') -- 2 spaces SET @StrVal = REPLACE(@StrVal, ' ', ' ') -- 2 spaces (for odd leftovers) RETURN @StrVal END 

Isso pode ser feito por function recursiva

 CREATE FUNCTION dbo.RemSpaceFromStr(@str VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS BEGIN RETURN (CASE WHEN CHARINDEX(' ', @str) > 0 THEN dbo.RemSpaceFromStr(REPLACE(@str, ' ', ' ')) ELSE @str END); END 

então, por exemplo:

 SELECT dbo.RemSpaceFromStr('some string with many spaces') AS NewStr 

retorna:

 NewStr some string with many spaces 

Ou a solução baseada no método descrito por @ agdk26 ou @Neil Knight (mas mais seguro)
ambos os exemplos retornam a saída acima:

 SELECT REPLACE(REPLACE(REPLACE('some string with many spaces' , ' ', ' ' + CHAR(7)), CHAR(7) + ' ', ''), ' ' + CHAR(7), ' ') AS NewStr --but it remove CHAR(7) (Bell) from string if exists... 

ou

 SELECT REPLACE(REPLACE(REPLACE('some string with many spaces' , ' ', ' ' + CHAR(7) + CHAR(7)), CHAR(7) + CHAR(7) + ' ', ''), ' ' + CHAR(7) + CHAR(7), ' ') AS NewStr --but it remove CHAR(7) + CHAR(7) from string 

Como funciona: insira a descrição da imagem aqui

Cuidado:
Char / string usado para replace espaços não deve existir no início ou no final da string e independente.

Encontrei isto enquanto procurava por uma resposta:

 SELECT REPLACE( REPLACE( REPLACE( LTRIM(RTRIM('1 2 3 4 5 6')) ,' ',' '+CHAR(7)) ,CHAR(7)+' ','') ,CHAR(7),'') AS CleanString where charindex(' ', '1 2 3 4 5 6') > 0 

A resposta completa (com explicação) foi extraída de: http://techtipsbysatish.blogspot.com/2010/08/sql-server-replace-multiple-spaces-with.html

Na segunda olhada, parece ser apenas uma versão ligeiramente diferente da resposta selecionada.

Esta é a solução via multiple replace, que funciona para qualquer string (não precisa de caracteres especiais, que não fazem parte da string).

 declare @value varchar(max) declare @result varchar(max) set @value = 'alpha beta gamma delta xyz' set @result = replace(replace(replace(replace(replace(replace(replace( @value,'a','ac'),'x','ab'),' ',' x'),'x ',''),'x',''),'ab','x'),'ac','a') select @result -- 'alpha beta gamma delta xyz' 

Método 1

O primeiro método é replace espaços extras entre palavras por uma combinação incomum de símbolos como um marcador temporário. Em seguida, você pode replace os símbolos de marcadores temporários usando a function de substituição, em vez de um loop.

Aqui está um exemplo de código que substitui o texto dentro de uma variável String.

 DECLARE @testString AS VARCHAR(256) = ' Test text with random* spacing. Please normalize this spacing!'; SELECT REPLACE(REPLACE(REPLACE(@testString, ' ', '*^'), '^*', ''), '*^', ' '); 

Teste de Tempo de Execução # 1: Em dez execuções deste método de substituição, o tempo médio de espera nas respostas do servidor foi de 1,7 milissegundos e o tempo total de execução foi de 4,6 milissegundos. Teste de Tempo de Execução # 2: O tempo médio de espera nas respostas do servidor foi de 1,7 milissegundos e o tempo total de execução foi de 3,7 milissegundos.

Método 2

O segundo método não é tão elegante quanto o primeiro, mas também faz o trabalho. Esse método funciona aninhando quatro (ou, opcionalmente, mais) instruções de substituição que substituem dois espaços em branco por um espaço em branco.

 DECLARE @testString AS VARCHAR(256) = ' Test text with random* spacing. Please normalize this spacing!'; SELECT REPLACE(REPLACE(REPLACE(REPLACE(@testString,' ',' '),' ',' '),' ',' '),' ',' ') 

Teste de Tempo de Execução # 1: Em dez execuções deste método de substituição, o tempo médio de espera nas respostas do servidor foi de 1,9 milissegundos e o tempo total de execução foi de 3,8 milissegundos. Teste de Tempo de Execução # 2: O tempo médio de espera nas respostas do servidor foi de 1,8 milissegundos e o tempo total de execução foi de 4,8 milissegundos.

Método 3

O terceiro método de replace espaços extras entre palavras é usar um loop simples. Você pode verificar os espaços extras em um loop while e usar a function replace para reduzir os espaços extras a cada iteração do loop.

 DECLARE @testString AS VARCHAR(256) = ' Test text with random* spacing. Please normalize this spacing!'; WHILE CHARINDEX(' ',@testString) > 0 SET @testString = REPLACE(@testString, ' ', ' ') SELECT @testString 

Teste de Tempo de Execução # 1: Em dez execuções desse método de substituição, o tempo médio de espera nas respostas do servidor foi de 1,8 milissegundos e o tempo total de execução foi de 3,4 milissegundos. Teste de Tempo de Execução # 2: O tempo médio de espera nas respostas do servidor foi de 1,9 milissegundos e o tempo total de execução foi de 2,8 milissegundos.

 update mytable set myfield = replace(myfield, ' ', ' ') where myfield like '% %' 

Tente isso ..

Você pode tentar isto:

 select Regexp_Replace('single spaces only','( ){2,}', ' ') from dual;