SQL Server SPACE() Function: Returns String with Spaces

In SQL Server, the SPACE() function returns a string with the specified number of spaces limited to 8000 spaces.

SPACE(number_of_spaces)

Parameters

number_of_spaces: A positive integer that indicates the number of spaces to be returned as a string.

Return Value

Returns a string with specified number of spaces. If number_of_spaces is a negative integer, then the SPACE() function returns NULL.

Example 1:

In this simple example, a string of 50 spaces is returned.

SELECT SPACE(50) AS Result;

Example 2:

The SPACE() function can be used for adding fewer spaces for the easy readability of a query.

SELECT 'Move' + SPACE(3) + 'away!' AS Result;

Example 3:

The following example shows a negative integer passed to the SPACE function. The result is NULL.

SELECT SPACE(-5) AS Result;

Example 4:

The SPACE() function can be used in the SELECT statement while concatenating columns, as shown below.

SELECT LastName + ',' + SPACE(2) + FirstName as FullName FROM Employee;

Note: To include spaces in Unicode data, or to return more than 8000 space characters, use the REPLICATE() instead of the SPACE() function.