SQL Server SPACE Function


By:

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

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

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

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

Using DATALENGTH and LEN with SPACE Function

The next examples shows that DATALENGTH counts spaces.

SELECT DATALENGTH(SPACE(2)) as NumberCharacters
datalength counts spaces

The following example shows that LEN does not count spaces as characters.

SELECT LEN(SPACE(2)) as NumberCharacters
len does not count spaces

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

Related Articles


Last Update: 11/10/2021




Comments For This Article

















get free sql tips
agree to terms