AppDividend
Latest Code Tutorials

SQL SPACE Function Example | SQL Server SPACE()

0

SQL SPACE function is used for returning a repeated sequence of space, i.e. a string with a specified number of spaces. In SQL Server (Transact-SQL), the SPACE function returns a string with a specified number of spaces. SPACE is one of the SQL String Functions, which is used to reverse the specified expression.

SQL SPACE Function

SPACE() function returns the string of the specified number of space characters.

The way SQL SPACE() function works is, you provide a number of spaces you need, and it will return the string of precisely that number of spaces.

Syntax

See the following SQL SPACE() function.

SELECT SPACE (count);

Parameters

Count: A positive integer that is used for specifying the number of spaces, and if the count is negative, then NULL will be returned.

Query 1

SELECT SPACE (4);

Output

 '    '

Query 2

SELECT SPACE (1);

Output

' '

Concatenation with Spaces

See the following query.

SELECT 'Space' + SPACE (1) + 'Function' + SPACE (1) + 'Tutorial';

Output

Space Function Tutorial

Negative Integer Value

The argument needs to be the positive value. If it’s the negative value, the result is NULL.

See the following query.

SELECT SPACE(-11) AS Result;

See the output.

+----------+
| Result   |
|----------|
| NULL     |
+----------+

 

Above were the examples to make clear how space function works. Let’s apply it to a table.

Consider table the Employee:

Emp_id First_name Last_name City State Salary
101 Rohit Raj Patna Bihar 30000
201 Shivam Rana Jalandhar Punjab 20000
301 Karan Kumar Allahabad Uttar Pradesh 40000
401 Suraj Bhakat Kolkata West Bengal 60000
501 Akash Cherukuri Vizag Andhra Pradesh 70000

 

Suppose if we want to display first_name and last_name under a single column named full_name separated by a space, then the following query has to be considered.

Query:

Select first_name + SPACE (1) + last_name AS Full_name from Employee;

Output

Full_name
Rohit Raj
Shivam Rana
Karan Kumar
Suraj Bhakat
Akash Cherukuri

 

Here, you can see that the full_name of every employee is displayed under a single column named Full_name.

A SPACE function can be used in the following versions of SQL Server (Transact-SQL):

SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005.

What About 1 or 2 Spaces

This is an excellent function for adding lots of spaces, but it can also improve the code readability when including the small number of spaces – mainly if the code contains many instances of adding spaces.

When using the SPACE(), you can see how many spaces in the instant, without having to count them.

For example, see if you can tell how many spaces are in the following:

SELECT 'App' + '  ' + 'dividend!';

Your first reaction might be the quick guess (say, “2 or 3”), before looking the little closer to check.

If you want to be 100% sure, you’d probably have to run your cursor over the space to count the number of spaces.

Now look at the following code:

SELECT 'App' + SPACE(2) + 'dividend!';

No need to guess. You can see the 2 written right into the code.

The value could be within the variable so that your code might look more like this:

SELECT 'App' + SPACE(@space_size) + 'dividend!';

Which obviously, provides no clue as to how many spaces there are without having to look it up.

However, if you already know the value of @space_size, you’re good to go.

Finally, SQL SPACE Function Example | SQL Server SPACE() Tutorial is over.

Recommended Posts

SQL REVERSE function

SQL RTRIM function

SQL LTRIM function

SQL TRANSLATE function

SQL Replicate function

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.