T-SQL Function: Add a space between all characters

Introduction

While I realize the title of this tip isn't grammatically correct (à la Paul McCartney's famous song, Live and Let Die, with its line, "If this ever changing world in which we live in"), it was simply the most succinct way to state the purpose of this function. The wordy, but unambiguous way to say it is this: "For any input string, return a string that has an additional space between each two characters, even when one of the input characters is itself a space". Yeah, we're not going to win a Grammy with that line, are we? Okay, enough of that. On to the purpose.

Background

Why would you need a string with e x t r a s p a c e s between the letters? Who knows. My own reason was to get some data into a SSRS 2008 Reporting Services report that would overlay the background image of a paper form, you know, the kind that is intended to be filled out by hand printed letters and then read into a computer system by a scanning device? Well, that report is another topic, but if a search led you here, you must have your own reason for needing your letters expanded with spaces.

Using the code

First let me state that I arrived at this code by simplifying and modifying some brilliant T-SQL code I found by a Ukrainian SQL programmer named Andriy, over on stackoverflow. The T-SQL code below is what you need to create a user defined function in SQL Server. I was using SQL Server 2005 in this case. However, it's the same in SQL Server 2008 and 2012. One thing to note, though, is that the online documentation for the STUFF function for SQL Server 2005 & 2008 left one wondering about its proper use. The syntax showed two instances of "character_expression", without making it clear which was the replacement expression:

STUFF ( character_expression , start , length ,character_expression)

Thankfully, the SQL Server 2012 documentation improves the syntax by mentioning the "replaceWith_expression", like so:

STUFF ( character_expression , start , length , replaceWith_expression)

-- =============================================
-- Author:        Paul Simpson
-- Create date:   7/19/2012
-- Description:   Puts spaces between letters. 
--                Example: "GEORGE" becomes "G E O R G E"
-- =============================================
ALTER FUNCTION udf_PutSpacesBetweenChars 

(@String VARCHAR(100))

RETURNS VARCHAR(100)
AS
BEGIN
   DECLARE @pos INT, @result VARCHAR(100); 
   SET @result = @String; 
   SET @pos = 2 -- location where we want first space 
   WHILE @pos < LEN(@result)+1 
   BEGIN 
       SET @result = STUFF(@result, @pos, 0, SPACE(1)); 
       SET @pos = @pos+2; 
   END 
   RETURN @result; 
END
GO


/* Try these input strings*/

-- print dbo.udf_PutSpacesBetweenChars('a') 

-- print dbo.udf_PutSpacesBetweenChars('ab') 

-- print dbo.udf_PutSpacesBetweenChars('abc')

Point of Interest

You will notice that the starting position for locating the first space is 2, and that after putting in a space, the WHILE loop's last statement (before going back to the top of the loop) adds 2 to the last marked position. This last statement has to add 2, because the space we just now inserted added 1 to the position. But, what about very short input strings? Won't this be a problem? Well, fortunately, the top of the WHILE loop ensures that we are only going to add a space if the position marker is less than the current length of our string, plus one. So, a string that began with two characters, and that now has a space between them, is three characters long. Our position marker was changed from 2 to 4 at the end of the first iteration, and 4 is greater than 3, so the loop is bypassed and the 3 character string is returned to the calling code.

Okay, what if our input string is only 1 character long, such as the letter 'a'? While it wouldn't seem to make sense to call this function for a 1 character string, it can easily happen if you've automated a stored procedure to call the function against whatever value is in a particular column of a row of data. No problem, you pass in a 1 character string, the @pos position marker is set to 2 before the start of the WHILE loop, it's compared to LEN(@result)+1 (which is LEN('a')+1, or 1+1), which is 2, and @pos is not less than that, so Voilà! We bypass the loop and return the same 'a' that was the input string.