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
![simple example t-sql space](/tutorialimages/9369_space.001.png)
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
![ltrim eliminate spaces](/tutorialimages/9369_space.002.png)
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
![SQL server columns with spaces](/tutorialimages/9369_space.003.png)
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
![concatenate null with spaces](/tutorialimages/9369_space.004.png)
Using DATALENGTH and LEN with SPACE Function
The next examples shows that DATALENGTH counts spaces.
SELECT DATALENGTH(SPACE(2)) as NumberCharacters
![datalength counts spaces](/tutorialimages/9369_space.005.png)
The following example shows that LEN does not count spaces as characters.
SELECT LEN(SPACE(2)) as NumberCharacters
![len does not count spaces](/tutorialimages/9369_space.006.png)
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
![t-sql count number of spaces](/tutorialimages/9369_space.007.png)