SQL SPACE Function

The SQL SPACE is is one of the SQL String Function, which is used to return the string of repeated spaces. In this article we will show you, How to use SPACE in SQL Server with example.

SQL SPACE Function Syntax

The basic syntax behind the SPACE in SQL Server is as shown below:

SPACE(Integer)

SQL SPACE Function Example 1

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

T-SQL CODE

-- SPACE in SQL example
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

OUTPUT

ANALYSIS

Below lines of code is used to declare two VARCHAR variables and assigning them some random data.

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

Next statement will combine the two variable along with SPACE(1) and produce the string result. It means, we are assigning single space between the two string variables. We also assigned new name to that result as ‘Result1’ using SQL ALIAS Column.

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

In the next line, We are adding five spaces in between the Learn, SQL Server, and 8 spaces between SQL Server, 2014

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

SQL SPACE Function Example 2

The SPACE in SQL also helpful when you are combining (concatenating) multiple columns. In this example,We are going combine the First name and Last Name columns present in the Employe column to get Full name, and to get the empty space we will use SPACE function. For this, We are going to use the below shown data

T-SQL CODE

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

OUTPUT

TIP: We used this SPACE(5) to get FIVE empty spaces.

SQL SPACE Function Example 3

In this example,We are going combine the First name, five spaces, Last Name, one space, string text = ‘is working as’, eight spaces, and Department name columns present in the Employe table.

T-SQL CODE

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

OUTPUT

Thank You for Visiting Our Blog