In SQL Server, you can use the T-SQL SPACE() function to generate a specific number of spaces.

This can be handy for adding spaces within a string, for example, when concatenating two or more strings.

The way it works is, you provide the number of spaces you need, and it will return a string of exactly that number of spaces.

Syntax

The syntax goes like this:

SPACE ( integer_expression )

Where integer_expression is a positive integer that indicates the number of spaces. If this is a negative value, NULL is returned.

Example 1 – Basic Usage

Here’s an example of how it works:

SELECT SPACE(40) AS Result;

Result:

+------------------------------------------+
| Result                                   |
|------------------------------------------|
|                                          |
+------------------------------------------+

If it’s not clear, that resulted in 40 spaces.

Example 2 – Concatenation with Spaces

This example might demonstrate the effect a bit better.

SELECT 'Keep' + SPACE(10) + 'away!' AS Result;

Result:

+---------------------+
| Result              |
|---------------------|
| Keep          away! |
+---------------------+

Example 3 – Negative Integer Value

The argument needs to be a positive value. If it’s a negative value, the result is NULL:

SELECT SPACE(-10) AS Result;

Result:

+----------+
| Result   |
|----------|
| NULL     |
+----------+

Example 4 – A Database Example

Here’s a simple example of using SPACE() to insert space in between two database columns when returned from a database:

USE Music;
SELECT TOP(7)
  ArtistName + SPACE(5) + AlbumName AS 'Artist and Album'
FROM Artists ar
INNER JOIN Albums al
  ON ar.ArtistId = al.ArtistId
INNER JOIN Genres g
  ON al.GenreId = g.GenreId
WHERE g.Genre = 'Rock';

Result:

+-------------------------------------------+
| Artist and Album                          |
|-------------------------------------------|
| Iron Maiden     Powerslave                |
| AC/DC     Powerage                        |
| Devin Townsend     Ziltoid the Omniscient |
| Devin Townsend     Casualties of Cool     |
| Devin Townsend     Epicloud               |
| Iron Maiden     Somewhere in Time         |
| Iron Maiden     Piece of Mind             |
+-------------------------------------------+

What About 1 or 2 Spaces?

This is a great function for adding lots of spaces, but it can also improve code readability when including a small number of spaces – especially if the code contains many instances of adding spaces.

When using SPACE() you can see how many spaces in an instant, without having to count them.

For example, see if you can you tell how many spaces are in the following:

SELECT 'Keep' + '  ' + 'away!';

Your first reaction might be a quick guess (say, “2 or 3”), before looking a little closer to check. To be 100% sure, you’d probably have to run your cursor over the space to count the number of spaces.

Now look at the following code:

SELECT 'Keep' + SPACE(2) + 'away!';

No need to guess. You can see 2 written right into the code.

Having said that, the value could be within a variable, so that your code might look more like this:

SELECT 'Keep' + SPACE(@space_size) + 'away!';

Which obviously, provides no clue as to how many spaces there are (without having to look it up). However, if you already know the value of @space_size you’re good to go.

Example 5 – Variable Space Size

Here’s an example that demonstrates the previous point.

Here, the number of spaces is defined within a variable. In this case it’s easy to see the value of the variable, as it’s defined in the line immediately preceding the SPACE() function (obviously, this isn’t always going to be the case):

DECLARE @space_size int
SET @space_size = 2
SELECT 'Keep' + SPACE(@space_size) + 'away!' AS Result
GO

Result:

+-------------+
| Result      |
|-------------|
| Keep  away! |
+-------------+

Unicode or More than 8000 Spaces?

Microsoft states that, to include spaces in Unicode data, or to return more than 8000 character spaces, you should use REPLICATE instead of SPACE.