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!
-------------------------