Click here to Skip to main content
11,805,316 members (65,534 online)
Click here to Skip to main content

T-SQL Function: Add a space between all characters

, 23 Jul 2012 CPOL 18.4K 1
Rate this:
Please Sign up or sign in to vote.
Expanding a string by inserting alternating single spaces between characters


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.


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))

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

/* 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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Paul M Simpson
Software Developer (Senior)
United States United States
Paul has been working in the software development field since 1997, almost exclusively with Microsoft technology, focusing on building websites and database driven web applications.

Prior to that, Paul worked as a biomedical equipment technician at several hospitals, and once enjoyed a brief career as a professional musician, playing bass guitar and singing in a band that focused primarily on music from the 1960's British Invasion era. If you're curious, they've got a few songs on their YouTube channel at

You may also be interested in...

Comments and Discussions

GeneralMy vote of 5 Pin
Lau, Janson12-Mar-15 8:02
memberLau, Janson12-Mar-15 8:02 
GeneralExactly what I was looking for. Pin
Lau, Janson12-Mar-15 7:39
memberLau, Janson12-Mar-15 7:39 
QuestionThis is how I would have done it Pin
SimpleDev14-Nov-14 14:29
memberSimpleDev14-Nov-14 14:29 
QuestionProper SQL Pin
Member 838407224-Feb-13 22:15
memberMember 838407224-Feb-13 22:15 
AnswerRe: Proper SQL Pin
Paul M Simpson21-Mar-14 16:36
memberPaul M Simpson21-Mar-14 16:36 
GeneralMy vote of 1 Pin
Member 852840924-Jul-12 5:09
memberMember 852840924-Jul-12 5:09 
GeneralRe: My vote of 1 Pin
Paul M Simpson14-Aug-12 11:48
memberPaul M Simpson14-Aug-12 11:48 
GeneralRe: My vote of 1 Pin
Member 852840930-Aug-12 5:31
memberMember 852840930-Aug-12 5:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.151002.1 | Last Updated 23 Jul 2012
Article Copyright 2012 by Paul M Simpson
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid