What is SPACE function in SQL Server?

Hello Folks,

Have you heard this name? If not, you don’t have to worry at all because I am going to give you some heads up:

• The main usage for the SPACE function is to replicate spaces for a string.
• Its syntax can be shown as:

SPACE ( integer_expression )

Here, integer_expression is a positive integer that indicates the number of spaces. If integer_expression is negative, a null string is returned.

• The following example trims the last names and concatenates a comma, two spaces, and the first names of people listed in the Person table in AdventureWorks2008R2:

Example from BOL:

USE AdventureWorks2008R2;
GO
SELECT RTRIM(LastName) + ',' + SPACE(2) +  LTRIM(FirstName)
FROM Person.Person
ORDER BY LastName, FirstName;
GO

The result can be seen as:

As you can see from the above that there is a gap of 2 characters between the ‘,’, and the first name, so this how SPACE function has been used.

• But the SPACE function will return a maximum of 8000 spaces only.
• Now if you also want to include spaces in Unicode data, or to return more than 8000 character spaces, you would have to use REPLICATE in the place for SPACE.

It will become clearer to you if you see the following example:

SELECT DATALENGTH(REPLICATE(CAST(' ' AS VARCHAR(MAX)),10000))

The result can be seen as:

Since replicate returns maximum of 8000 characters so we need to convert the string to VARCHAR (MAX) data type to have more characters.

Well this was all about SPACE function used in SQL Server.

And also comments on this!!

Regards

Piyush Bajaj

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook