Usuwanie podwójnych spacji z tekstu metodami języka SQL

Załóżmy, że w tabeli w danej kolumnie mamy zapisany ciąg znaków, jednak z jakiegoś powodu w tekście pojawiają się podwóje spacje (lub inne podwójne znaki). Jak ich się pozbyć przy pomocy metod dostępnych w języku SQL?
Po kolei. Załóżmy, że tą naszą kolumną jest zmienna @string. Można by próbować oczyścić napis z niepotrzebnych podwójnych spacji z wykorzystaniem funkcji REPLACE. Każemy tej funkcji zamieniać podwójne spacje ’  ’ na pojedyńcze. W ten sposób z np. 4 spacji zrobią się najpier 3, w kolejnym kroku 2, a w ostatnim tylko jedna:

DECLARE @string VARCHAR(100);
SET @string = 'Very   long  string    with  multiple    spaces';

SELECT @string;
SELECT @string = REPLACE(@string, '  ', ' ');
SELECT @string;
SELECT @string = REPLACE(@string, '  ', ' ');
SELECT @string;
SELECT @string = REPLACE(@string, '  ', ' ');
SELECT @string;
SELECT @string = REPLACE(@string, '  ', ' ');
SELECT @string;
GO

Niby dobrze, ale kiedy należy skończyć z takim wywoływaniem procedury? Ano wtedy, gdy w napisie nie ma już podwójnych spacji!
Pisząc pętlę while jesteśmy coraz bliżej dobrego rozwiązania. Warunkiem zakończneia pętli będzie sprawdzenie czy dane zawierają jeszcze jakieś podwójne spacje. Zrobi to funkcja CHARINDEX. Szuka ona wystąpienia określonego znaku/znaków w napisie i zwaraca liczbę będącą pozycją szukanego znaku w kolumnie. Jeżeli funkcja zwraca 0, to znaczy, że w napisie nie ma już podwójnych spacji:

DECLARE @string VARCHAR(100);
SET @string = 'Very   long  string    with  multiple    spaces';

WHILE (CHARINDEX('  ',@string)>0)
 SELECT @string = REPLACE(@string, '  ', ' '); -- zamień 2 spacje na 1

SELECT @string;
GO

Skoro to działa, to jesteśmy już o krok od sukcesu. Napiszmy funkcję, która jako argument przyjmuje napis do oczyszczenia ze spacji, a zwraca napis oczyszczony, z którego usunięto już zdublowane spacje.

CREATE FUNCTION CleanSpaces(@Input VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
 WHILE (CHARINDEX('  ',@Input)>0)
  SELECT @Input = REPLACE(@Input, '  ', ' '); -- zamień 2 spacje na 1

RETURN @Input;
END
GO

DECLARE @string VARCHAR(100);
SET @string = 'Very   long  string    with  multiple    spaces';

SELECT @string,dbo.CleanSpaces(@string)
GO

Uff – spacje wyrzucone, problem rozwiązany. Podwóje spacje zostały usunięte z kolumny. Pora na sztuczkę, która zrobi to jeszcze szybciej!
Załóżmy, że mam napis z 3 spacjami

New   York

Gdyby tak każdą spację zamienić na <> to napis zmieniłby postać na:

New<><><>York

A gdyby tak teraz każdy ciąg znaków >< zamienić na pusty napis:

New<>York

I wreszcie gdyby każdy ciąg znaków <> zamienić na spację to dostałbym:

New York

Zamieniając nasze gdybbanie na język SQL:

DECLARE @string VARCHAR(100);
SET @string = 'New York';

SELECT REPLACE(REPLACE(REPLACE( @string, ' ', '<>'),'><', ''),'<>',' ')

No brawo, bez pisania włsanych funkcji, ale z wykorzystaniem sprytu, można usunąć podwójne spacje wykorzystując SQL.

Źródła:

• http://www.sqlusa.com/bestpractices2005/removemultiplespaces/
• http://www.itjungle.com/fhg/fhg101106-story02.html
• http://social.msdn.microsoft.com/Forums/en/sqlgetstarted/thread/bbb2bf6a-ca04-49ed-9277-b2f430a93a7b