space() function in SQL
This function returns a string with a specified number of spaces. If we specify a number, it returns the strings having that specified number of spaces. SPACE Function is available in MySQL Server also.
Syntax:
SPACE (number)
SPACE Function only accepts a single parameter. In that the number denotes the specific number of spaces.
This Function will return a value:
- If the provided number is a positive integer, then the Function will return a string having the specified number of spaces.
- As the number of spaces should only be positive, if we give a negative number the function will return NULL.
Space Function is applicable in the below mentioned versions:
- SQL Server 2005
- SQL Server 2008
- SQL Server 2008 R2
- SQL Server 2012
- SQL Server 2014
- SQL Server 2016
- SQL Server 2017
Let’s now look into various examples which describes the functionality of the SPACE Function.
Example1:
SELECT SPACE (20) AS SOLUTION;
Output:
Solution
------------------------------------------|
------------------------------------------|
In the above we are resulted with 20 spaces as the solution.
Example2:
With this example we can understand the functionality of the SPACE Function much better.
SELECT ‘Hello’ + SPACE(15) + ‘World!’ AS Solution;
Output:
Solution
---------------------------
Hello World!
---------------------------
As we mentioned a SPACE of 15 characters in the middle of “hello” and “world!”, we are given the same in the output as Solution.
Example3:
When we give a negative value as an argument to the SPACE Function, it returns a NULL value.
SELECT SPACE ( -5) AS Solution;
Output:
Solution
--------------
NULL
--------------
So, as the argument given is a negative integer we are produced with the NULL Value.
Example4:
Consider a Student Table having ID, FirstName, LastName, and gender as the Columns/Fields of the table.
To print all the student names with specified number of spaces in between the FirstName and LastName, we will write the below query.
SELECT FirstName + SPACE(7) + LastName AS ‘Student Name’ FROM Student;
Output:
Student Name
----------------------------
Sandeep Jain
Kiran Priya
Alia Bhatt
Kaira Adwani
----------------------------
The main Advantage of using SPACE Function is that we can get to know the number of spaces present between two strings.
Consider the below query
select ‘Data’ + ‘ ‘ + ‘Structures’ ;
In here we could not guess the number of spaces in between the two strings. At random we can guess the number as 3 or 4 spaces.
If we write the same query using SPACE Function,
select ‘Data’ + SPACE (3) + ‘Structures’ ;
we can easily determine the number of spaces in between the given two strings.
Example5:
We can also give a variable as the argument into the SPACE() Function. So, we should declare the value of the variable before giving it into the Function.
DECLARE @size_of_space int
SET @size_of_space = 6
select ‘Six Space’ + SPACE(@size_of_space) + ‘Away!’ as Solution;
Output:
Solution
Solution
-------------------------
Six Space Away!
-------------------------