SPACE() function in SQL Server

SPACE() :
This function in SQL Server helps to return a string that has a specified number of spaces. This function is also available in MYSQL with the same name.

Syntax :

SPACE(number)

Parameters :
This function accepts only one parameter.

• number –
  It demotes the number of spaces.

Returns :

• If the number is positive, the function will return a string that has a specified number of spaces.
• If the number is negative, the function will return NULL.

Applicable in the following versions :

• SQL Server 2017
• SQL Server 2016
• SQL Server 2014
• SQL Server 2012
• SQL Server 2008 R2
• SQL Server 2008
• SQL Server 2005

Example-1 :
Basic working of SPACE() function, the function will return 5 blank spaces.

SELECT SPACE(5) 
AS Result;

Output :
The function will return 5 blank spaces.

Result
‘     ‘

Example-2 :
Concatenating 2 or more strings using SPACE() function.

• Concatenating 2 strings using the SPACE() function, we take 2 strings and assign specific spaces in between.

  SELECT 'HI!' + SPACE(6) + 'NiceToSeeYou' 
  AS Result;

  Output :

  Result
  HI! NiceToSeeYou

• Concatenating 3 strings using SPACE() function, we take 2 strings and assign specific spaces among them.

  SELECT 'GEEKS' + SPACE(5) + 'FOR'+ SPACE(5) +'GEEKS' AS Result;

  Output :

  Result
  GEEKS     FOR GEEKS

Example-3 :
If the user enters a negative number as an argument, then the function will return NULL.

SELECT SPACE(-20) 
AS Result;

Output :

Result
NULL

Example-4 :
Working of SPACE() function with variable, we use variable along with SPACE() function.

DECLARE @space_Size int
SET @space_Size = 7
SELECT 'KeepChasing' + SPACE(@space_size) + 'YourDreams' 
AS Result;

Output :

Result
KeepChasing       Your Dreams