SQL Server SPACE() Function

The SQL Server (Transact-SQL) SPACE() function returns a string with a specified number of spaces.

Syntax

SPACE(number)

Parameters

number Required. Specify the number of spaces to be returned.

Return Value

Returns a string with a specified number of spaces.

Example 1:

The example below shows the usage of SPACE() function.

SELECT SPACE(5);
Result: '     '

SELECT SPACE(10);
Result: '          '

Example 2:

Consider a database table called Employee with the following records:

EmpID	FirstName	LastName
1	John	Smith
2	Marry	Knight
3	Jo	Williams
4	Kim	Fischer
5	Ramesh	Gupta
6	Huang	Zhang

In the query below, the SPACE() function is used to add specified number of spaces while concatenating the records of column FirstName and column LastName.

SELECT *, (FirstName + SPACE(2) + LastName) AS FullName FROM Employee;

This will produce the result as shown below:

EmpID	FirstName	LastName	FullName
1	John	Smith	John Smith
2	Marry	Knight	Marry Knight
3	Jo	Williams	Jo Williams
4	Kim	Fischer	Kim Fischer
5	Ramesh	Gupta	Ramesh Gupta
6	Huang	Zhang	Huang Zhang

❮ SQL Server Functions