Click here to Skip to main content
Click here to Skip to main content

T-SQL Function: Add a space between all characters

By , 23 Jul 2012
Rate this:
Please Sign up or sign in to vote.

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.

License

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 http://www.youtube.com/echo60smusicband

Comments and Discussions

 
QuestionProper SQL PinmemberMember 838407224-Feb-13 22:15 
AnswerRe: Proper SQL PinmemberPaul M Simpson21-Mar-14 16:36 
GeneralMy vote of 1 PinmemberMember 852840924-Jul-12 5:09 
GeneralRe: My vote of 1 PinmemberPaul M Simpson14-Aug-12 11:48 
GeneralRe: My vote of 1 PinmemberMember 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 | Mobile
Web02 | 2.8.140421.2 | Last Updated 23 Jul 2012
Article Copyright 2012 by Paul M Simpson
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid