SQL Server SPACE() function

The SQL Server SPACE() function is a handy tool for generating a string of space characters. It is particularly useful for formatting and padding data within SQL queries. By specifying the number of spaces, users can ensure consistent spacing in output results, which can be critical for aligning text and enhancing readability.

In this article, We will learn about the SQL Server SPACE() function in detail by understanding various examples.

SQL Server SPACE() function

• The SPACE() function in SQL Server is used to generate a string of spaces.
  
• It can be useful for formatting or padding data within SQL queries.

Syntax

SPACE ( integer_expression )

• integer_expression: The number of spaces to be included in the resulting string. This must be a positive integer.
  
• The SPACE() function returns a string consisting of a specified number of space characters.
  
• If the integer_expression is negative, the function returns an empty string.

Parameters:

The SPACE() function accepts only one parameter.

• number: It denotes 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.

Examples of SQL Server SPACE() function

Let's look at some examples of the SPACE() function in SQL Server

Example 1:

In this example, we will return 5 blank spaces.

SELECT SPACE(5) 
AS Result;

Output:

Example 2:

In this example, we will concatenate 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:

Example 3:

In this example, we will enter a negative number as an argument.

SELECT SPACE(-20) 
AS Result;

Output:

Note: If we pass negative value in SPACE() function, it will return NULL in results.

Example 4:

In this example, we are will check working of SPACE() function with variable.

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

Output:

Explanation: This query concatenates the string 'KeepChasing' with a dynamically determined number of space characters (7 in this case) and then appends 'YourDreams'. The result is a single string where 'KeepChasing' and 'YourDreams' are separated by 7 spaces.

Conclusion

The SPACE() function in SQL Server provides a straightforward way to insert a specified number of space characters into a string. Whether for formatting output, padding data, or aligning text in query results, the function offers flexibility with a simple syntax. It's important to note that passing a negative number will result in a NULL output, which can be used to handle exceptions or errors in data processing.

vanshgaur14866

Follow

Improve

Article Tags :

• Technical Scripter
• SQL
• Databases
• Technical Scripter 2020
• DBMS-SQL
• SQL-Server

+2 More