Recently I was reviewing a stored procedure written to locate or remove white space from a string. The procedure used a pointer to loop through the string, addressing each character one at a time. If the character was white space, carriage return, line feed or tab, then it was excluded from the final string. Otherwise, the character was concatenated with a final output.
My initial thought was to simply use the REPLACE command built into SQL Server. You would need 4 replace commands
REPLACE(REPLACE(REPLACE(REPLACE(@String, ‘ ‘,’’), CHAR(13), ‘’),CHAR(10), ‘’), CHAR(9), ‘’).
Since the REPLACE operation is part of the SQL base language I have found it works much faster.
Let’s say you don’t have REPLACE as an operation available to you, you can take advantage of a tally table to do the processing in a single query. A Tally table has one column, number. It contains a sequence of numbers from 1 to as many as you need. The following query performs the same function as the original example with less work.
DECLARE @String VARCHAR(1000) =
‘THIS IS A STRING WITH WHITE SPACE’ +
CHAR(13) + CHAR(10) + CHAR(9)
DECLARE @Result VARCHAR(1000) = '' -- initialize the variable or you end up with null
SELECT @Result += SUBSTRING (@string, Number, 1)
FROM Tally
WHERE Number BETWEEN 1 AND LEN(@String)
AND SUBSTRING(@String, Number, 1) NOT IN (‘ ‘, CHAR(13), CHAR(10), CHAR(9))
The value @Result will now have all the characters found in @String without white space.
THISISASTRINGWITHWHITESPACE
Set operations rock in SQL Server. A Tally table provides you with many opportunities to do things as sets.
Do you use Tally tables often? I have found them indispensable. Why not share some of your favorite uses for a Tally table. Leave your comment here or drop me a line at btaylor@sswug.org.
Cheers,
Ben