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.
SELECT SPACE (count);
Count: The positive integer that is used for specifying the number of spaces, and if the count is negative, then NULL will be returned.
SELECT SPACE (4);
SELECT SPACE (1);
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:
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;
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.