In this SQL Server tutorial, I will explain the SPACE function in SQL Server, which helps generate spaces.
First, I will describe the SPACE function in SQL Server with an example, then the syntax of this function. After this, several examples to clear your concept of how the SPACE() function works.
While doing this example, you will learn how to use the SPACE() function when concatenating multiple strings to improve readability.
Finally, you will learn to use the SPACE() function on the table column values.
SPACE Function in SQL Server
The SPACE function in SQL Server returns the specified number of space characters.
For example, when you need to write the first and last name of the person, to give space between them, you usually use the space bar from the keyboard, but here in the database, you can use the SPACE() function to give or insert the space characters between the strings.
The syntax is given below.
SPACE(number_of_spaces)Where,
- SPACE(): This function generates the number of space characters.
- number_of_spaces: It is the integer value, which means how many spaces you want to generate, such as one, two, or five spaces. If you pass the negative value, the SPACE() function returns the NULL value.
The returns type of the SPACE() function varchar returns the space character of the type string.
For example, use the query below to generate the three space characters using the SPACE function in SQL Server.
SELECT SPACE(3) AS Spaces;
After executing the above query, SPACE(3) returns the three space characters which are not visible, or these kinds of characters are always invisible.
But let’s see examples of where we will concatenate the two strings, ‘United’ and ‘States’. Now, use the query below to combine both strings.
SELECT 'United'+'States' CombinedString;
As you can see, both strings ‘United’ and ‘States’ are combined as UnitedStates; here, the plus (+) operator is used to concatenate the string values.
But as you can see, there is no space between the two words to specify or insert the space character between the words; here, you can use the SPACE() function.
For example, insert the one space between the words ‘United’ and ‘States’ using the query below.
SELECT 'United'+SPACE(1)+'States' CombinedStringWithSpace;
Now, you can see a gap between the two words; that gap is the one space inserted or concatenated using SPACE(1) in the query.
Instead of one space like this SPACE(1), you can generate any number of spaces by increasing the integer value with the SPACE function in SQL Server.
For example, again, take the above example, but specify the number of spaces around 6, as shown in the query below.
SELECT 'United'+SPACE(6)+'States' CombinedStringWithSpace;
As you can see from the query output, the gap between the two words increases because six spaces are concatenated with those words using SPACE(6).
This is the concept of the SPACE in SQL Server, which allows you to generate a specified number of space characters.
Next, let’s see how to use the variable within the SPACE() function.
Using Variable within SPACE function in SQL Server
So, in the previous section, the number of spaces is specified as an integer value to the SPACE() function. Still, you can also pass the number of spaces using the variable which contains the integer value.
For example, declare the variable name as sp, assign a value equal to 2, and use this variable within the SPACE() function as shown below.
DECLARE @sp INTEGER = 2 ;
SELECT 'I am from'+SPACE(@sp)+'Australia';
The above output generates two spaces and concatenates between the strings ‘I am from ‘ and ‘Australia’ using the variable @sp in the SPACE() function. This @sp contains the integer value 2, which tells the SPACE() function to return the two space characters.
This is how you can use the variable within the SPACE function in SQL Server to specify the number of space characters.
Now, it is time to see how to use the SPACE() function on the table columns.
SPACE Function in SQL Server on Table Column
Till now, you have used the SPACE() function to return (generate) the specified number of spaces using the integer value or variable and concatenated the spaces with strings.
Here, you will learn how to use the SPACE function in SQL Server on the table column so that you can insert or generate the spaces for the column values or use the SPACE() function as your requirement arises when you work with a table.
For example, your database has an Employees table, as shown below.
From the above table, you have a task to combine the first and last names of the employees separated by space. For that, use the query below.
SELECT FirstName + SPACE(1) + LastName AS FullName FROM Employees;
As you can see in the result set, the FullName column contains the employees’ first and last names separated by one space. For example, Jax Moore and Brady Perez, each full name contains the space between the first and last name.
In the query part, FirstName + SPACE(1) + LastName concatenates the values of the FirstName column, one space using SPACE(1), and the values of the LastName column as the full name of an employee where the employee’s first and last names are separated by one space.
You can use the SPACE function in SQL Server in several ways, such as formatting output for reports, increasing the readability of the concatenated strings as you have combined the employee’s names, etc.
- Remember, when you have to generate several spaces such as 1, 3, or 6, sometimes as the requirement, you will need to generate more space characters, so in that case, you can generate around 7990 or 8000 space using the SPACE() function.
- To generate more spaces more than 8000, consider using the REPLICATE() function in SQL Server.
Conclusion
In this SQL Server tutorial, you have generated the number of spaces using the SPACE function in SQL Server, where you have concatenated the space with string values and generated the space characters using the variable in the SPACE() function.
Finally, you have applied the SPACE() function on the table columns to concatenate the employee’s first and last name with space.
You may like to read:
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.