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:

1_SQL_Server_What_is_SPACE_function

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:

2_SQL_Server_What_is_SPACE_function

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

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

2 Comments on “What is SPACE function in SQL Server?”

Leave a Reply

Your email address will not be published. Required fields are marked *