SQL SPACE() Function: The Complete Guide

THE SQL SPACE() function is used to return a repeated sequence of space, i.e., a string with a specified number of spaces.

SQL SPACE Function

SQL SPACE() is a built-in function that returns the string of the specified number of space characters. So the way the SQL SPACE() function works is, you provide a number of spaces you need, and it will return the string of precisely that number of spaces.

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


See the following SQL SPACE() function.



Count: The positive integer that is used for specifying the number of spaces, and if the count is negative, then NULL will be returned.

Query 1



 '    '

Query 2



' '

Concatenation with Spaces

See the following query.

SELECT 'Space' + SPACE (1) + 'Function' + SPACE (1) + 'Tutorial';


Space Function Tutorial

Negative Integer Value

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

See the following query.

SELECT SPACE(-11) AS Result;

See the output.

| Result   |
| NULL     |


Above were the examples to make clear how space function works. Next, let’s apply it to a table.

Consider table the Employee:

Emp_id First_name Last_name City State Salary
101 Rohit Raj Patna Bihar 30000
201 Shivam Rana Jalandhar Punjab 20000
301 Karan Kumar Allahabad Uttar Pradesh 40000
401 Suraj Bhakat Kolkata West Bengal 60000
501 Akash Cherukuri Vizag Andhra Pradesh 70000


Supe if we want to display first_name and last_name under a single column named full_name separated by a space, then the following qmustas to be considered.


Select first_name + SPACE (1) + last_name AS Full_name from Employee;


Rohit Raj
Shivam Rana
Karan Kumar
Suraj Bhakat
Akash Cherukuri


Here, you can see that the full_name of every Employee is displayed under a single column named Full_name.

A SPACE function can be used in the following versions of SQL Server (Transact-SQL):

SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005.

What About 1 or 2 Spaces

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

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

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

SELECT 'App' + '  ' + 'dividend!';

Your first reaction might be the quick guess (say, “2 or 3”) before looking the little closer to check.

If you want 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 'App' + SPACE(2) + 'dividend!';

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

The value could be within the variable so that your code might look more like this:

SELECT 'App' + SPACE(@space_size) + 'dividend!';

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.

That’s it for this tutorial.

Recommended Posts

SQL REVERSE function

SQL RTRIM function

SQL LTRIM function


SQL Replicate function

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.