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 'Beginner' + SPACE(5) + 'FOR'+ SPACE(5) +'Beginner' AS Result;
Output :
Result Beginner FOR Beginner
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 |