Interview Question of the Week #042 – How Does SPACE Function Works in SQL Server?

This question may sound strange but I have personally observed this being asked in one of the interview. The question was how the SPACE function works in SQL Server.

Question: How Does SPACE Function Works in SQL Server?

Answer: SPACE function accepts one numeric parameter and generates as many as space as a value of the parameter.

Observe the output of the following query:

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

You will notice there is gap of five space between two commas.

Useful Details About How the SPACE Function Works

The function looks simple, but a few details come up again and again. If you pass a negative number or NULL, SPACE returns NULL, so a calculated length that drops below zero can quietly turn your whole string into NULL when you join it with +. If the length comes from a calculation, guard it with a CASE expression so it never goes below zero.

It returns VARCHAR data. For more than 8,000 spaces, or for spaces inside Unicode text, Microsoft recommends REPLICATE instead, for example REPLICATE(N' ', 10).

A common real use is fixed width output, such as a text file for an older system. LEFT(Name + SPACE(20), 20) pads a name to exactly 20 characters, and RIGHT(SPACE(10) + CAST(Amount AS VARCHAR(10)), 10) lines up numbers on the right side.

One thing surprises people when they test this. LEN(SPACE(5)) returns 0, because LEN does not count trailing spaces. Use DATALENGTH(SPACE(5)), which returns 5, when you want to see that the spaces are really there. For the same reason, SQL Server treats ‘abc’ and ‘abc’ followed by a few spaces as equal in a normal = comparison, so trailing spaces rarely cause a mismatch in a WHERE clause, but they do change the length of what you export.

For padding with other characters, REPLICATE is the tool. RIGHT(REPLICATE('0', 6) + CAST(Id AS VARCHAR(6)), 6), for example, turns 42 into 000042. On SQL Server 2012 and later, FORMAT(42, '000000') gives the same result and reads nicely, but it is noticeably slower on large result sets, so I keep it for small ones.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.