Powerful, Flexible Text-Formatting Solutions in SQL Server

emoving extra empty spaces is a pretty common task that seems very simple, but in reality it is not as trivial as you may assume. The first impulsive solution that many programmers try is replacing two empty spaces with one, which works as long as the number of empty spaces doesn't exceed two. The problem starts when text has three, four, or more empty spaces between words. If you try repeatedly replacing every two empty spaces with one in those scenarios, you can completely remove some of the empty spaces between the words.

The conventional approach for such a task would be checking for empty spaces, character by character, in a loop and removing the extra spaces. However, there is another approach (see Listing 1).

Listing 1. Removing Extra Empty Spaces



SET QUOTED_IDENTIFIER OFF
GO
SET NOCOUNT ON
DECLARE @str varchar(2000)
SELECT @str = " Bipartite graphs are useful for modelling matching problems."
CREATE TABLE #words(singleWord varchar(50))
SELECT @str = REPLACE(@str, CHAR(13), ' ');
SELECT @str = REPLACE(@str, CHAR(10), ' ');
-- Split by words and load text into the table
SELECT @str = 'INSERT INTO #words(singleWord) SELECT A="' +
REPLACE(@str, ' ', '"UNION ALL SELECT"') + '"'
EXECUTE(@str);
-- Delete spaces between the words
SELECT * FROM #words;
DELETE #words WHERE singleWord = '';
SELECT * FROM #words;
-- Restore original text without extra empty spaces
SELECT @str = '';
SELECT @str = @str + singleWord + ' ' FROM #words;
SELECT @str;
DROP TABLE #words