SQL SPACE

The SQL SPACE function is one of the String functions that is used to return the string of repeated spaces. The syntax of the SPACE() function is as shown below.

SPACE(Integer)

It accepts any positive integer argument that indicates the total number of spaces. For instance, 2 means the SPACE() function returns 2 spaces. When we pass a negative number, it returns NULL.

SQL SPACE Function Example

The String SPACE Function returns the string of repeated spaces. The following query will show you multiple ways to use this function.

Example 1: Add spaces between two strings

In the example query, we add two blank spaces between Tutorial and Gateway.

SELECT 'Tutorial' + SPACE(2) + 'Gateway'

Tutorial  Gateway

Similarly, we can use a combination of CONCAT() and SPACE() functions to create a sentence with a meaningful name. For example, a full name can be created by concatenating the first and last name with a space.

SELECT CONCAT('Will', SPACE(1), 'Smith')

Will Smith

Example 2: Use SQL Server SPACE() function on variable

Within this example query, it will combine the two variables along with SPACE(1) and produce the string result. It means we are assigning a single space between the two string variables and assigning a new name using the ALIAS Column. Please refer to the list of SQL String Functions from our SQL Server Programming.

In the next line, We are adding five whitespaces in between the first two, and 8 spaces between Server, 2014.

DECLARE @Str1 VARCHAR(50), 
        @Str2 VARCHAR(50)
SET @Str1 = 'Learn ' 
SET @Str2 = 'SQL Server'

SELECT @Str1 + SPACE(1) + @Str2 AS Result1 
SELECT @Str1 + SPACE(3) + @Str2 AS Result2 

SELECT 'Learn' + SPACE(5) +'SQL Server'+ SPACE(8) + '2014' AS Result3

Example 3: Use SPACE() function with NULL and Negative value

In this query, we pass a NULL value and a negative value to the SPACE() function, and it returns NULL.

SELECT 'Hi' + SPACE(NULL) + 'Smith'
SELECT 'Hi' + SPACE(-5) + 'Smith'

NULL
NULL

SQL SPACE Function on table columns

The SPACE function is also helpful when combining multiple columns (concatenating). In this example, we will combine the First name and Last Name columns present in the Employee column to get the Full name and the space. We will use this function.

For this SPACE method demonstration, We are going to use the below-shown data.

SELECT [FirstName]
      ,[LastName]
      ,[FirstName] + SPACE(5) + [LastName] AS [Full Name]
      ,[DepartmentName]
      ,[Email]
  FROM [Employe]

TIP: We used this SPACE(5) String Function to get FIVE empty whitespaces. Please refer to the SQL REPLICATE and SQL CONCAT functions.

Use SPACE() function to join strings

In this string SPACE function example, we will combine the First name, five spaces, Last Name, one space, string text = ‘is working as’, eight whitespaces, and the Department name columns available in the Employee table.

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
  	  ,[FirstName] + SPACE(5) + [LastName] + SPACE(1) + 
	  'is Working as' + SPACE(8) + [DepartmentName] AS [Description]
      ,[Email]
  FROM [Employe]