SQL Server SPACE Function
By: Daniel Calbimonte
The SPACE function is used to add a specific number of spaces into a string. It is a best practice to use this function instead of a writing a bunch of spaces because it is easier to read the number of spaces being added.
Syntax
SPACE(NumberOfRepetitions)
Parameters
- NumberOfRepetitions - this is an integer value with the number of times that the space will be repeated. If a negative value, the value returned by the function is NULL.
Simple SPACE Example
The following example will concatenate the word "Hello" with 5 spaces and then word "world".
SELECT CONCAT('Hello',SPACE(5),'world') as msg

LTRIM with SPACE Functions Together
In the following example, we are adding 3 spaces and then using LTRIM function to remove the spaces.
SELECT LTRIM(CONCAT(SPACE(3),'Hello')) as msg

Using SPACE Function with Table Columns
The next example is concatenating FirstName, a space and then the LastName.
SELECT CONCAT(Firstname,SPACE(1),LastName) as fullname FROM Person.Person

Concatenating NULL values with SPACE Function
The next example will concatenate NULL values with SPACE(1). The result is one space.
SELECT CONCAT(NULL,SPACE(1),NULL) as msg

Using DATALENGTH and LEN with SPACE Function
The next examples shows that DATALENGTH counts spaces.
SELECT DATALENGTH(SPACE(2)) as NumberCharacters

The following example shows that LEN does not count spaces as characters.
SELECT LEN(SPACE(2)) as NumberCharacters

Counting Number of Spaces
Finally, we will compare a string named @string with 5 spaces with the @comparestring which is @string without spaces and then we will calculate the datalength of both variables.
DECLARE @string varchar(50) = CONCAT('count',SPACE(5),'spaces') -- remove spaces DECLARE @comparestring varchar(50) = REPLACE(@string,' ','') SSELECT DATALENGTH(@string) - DATALENGTH(@comparestring) as numberOfSpaces
