When working with databases and the associated data, sometimes you need to insert a space when joining two strings together, or sometimes you need to replace a character with a space.

And then sometimes you need to insert multiple spaces. Here are 3 ways to return a string of multiple spaces in SQL Server using T-SQL.

Option 1: Type out all the Spaces

The most obvious way is to simply type out each space you need. Like this:

SELECT 'Homer' + '            ' + 'Satriani';

Result:

-------------------------
Homer            Satriani

In this example I add 12 spaces in between two other strings. This method is usually perfect when you only need to insert one or two spaces. But once you need to add say, a dozen or more, then it can be easier to use the SPACE() function.

Option 2: The SPACE() Function

The SPACE() function was built specifically for this purpose – to return a string of repeated spaces. Simply type the function and provide an argument that specifies how many spaces are required.

So we can convert the previous example into this:

SELECT 'Homer' + SPACE(12) + 'Satriani';

Result:

-------------------------
Homer            Satriani

Option 3: The REPLICATE() Function

If you need to insert a seriously large number of spaces (8000 or more), then you should use the REPLICATE() function. This function actually replicates any character – not just spaces.

So we can change the previous examples to the following:

SELECT 'Homer' + REPLICATE(' ', 12) + 'Satriani';

Result:

-------------------------
Homer            Satriani

You could also use the CHAR() function to explicitly state the character to use. For example, CHAR(32) for a space or CHAR(160) for a non-breaking space:

SELECT 'Homer' + REPLICATE(char(160), 12) + 'Satriani';

Result:

-------------------------
Homer            Satriani

Replacing a Space with Multiple Spaces

So we could use any of the above methods within a REPLACE() function to replace a single space with multiple spaces.

Here’s an example using the SPACE() function:

SELECT REPLACE('Homer Satriani', ' ', SPACE(12));

Result:

-------------------------
Homer            Satriani