Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Transact-SQL Set Operators (UNION, EXCEPT and INTERSECT)
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

The SPACE string function returns a string of repeated spaces.  The syntax of the SPACE string function is as follows:

SPACE ( < integer_expression > )

The < integer_expression > is a positive integer that indicates the number of spaces.  If < integer_expression > is negative, a NULL string is returned.

Here are sample uses of the SPACE string function

Usage #1 : Right-Align Text

DECLARE @Header1 VARCHAR(25) = 'SQL Server Helper, LLC'
DECLARE @Header2 VARCHAR(25) = '1234 1st Street'
DECLARE @Header3 VARCHAR(25) = 'Somewhere City, NY 01001'
PRINT SPACE(50 - LEN(@Header1)) + @Header1
PRINT SPACE(50 - LEN(@Header2)) + @Header2
PRINT SPACE(50 - LEN(@Header3)) + @Header3
 
Output
---------------
                            SQL Server Helper, LLC
                                   1234 1st Street
                          Somewhere City, NY 01001

Usage #2 : Center-Align Text

DECLARE @Header1 VARCHAR(25) = 'SQL Server Helper, LLC'
DECLARE @Header2 VARCHAR(25) = '1234 1st Street'
DECLARE @Header3 VARCHAR(25) = 'Somewhere City, NY 01001'
PRINT SPACE((50 - LEN(@Header1))/2) + @Header1
PRINT SPACE((50 - LEN(@Header2))/2) + @Header2
PRINT SPACE((50 - LEN(@Header3))/2) + @Header3

 
Output
------------
              SQL Server Helper, LLC
                 1234 1st Street
             Somewhere City, NY 01001

Usage #3 : Justify Text

DECLARE @Contents TABLE ( 
    [Title]         VARCHAR(50),
    [PageNumber]    INT
)

INSERT INTO @Contents ( [Title], [PageNumber] )
VALUES ( 'Introduction', 1)

INSERT INTO @Contents ( [Title], [PageNumber] )
VALUES ( 'Table of Contents', 2)

INSERT INTO @Contents ( [Title], [PageNumber] )
VALUES ( 'Index', 100)

SELECT [Title] + SPACE(50 - LEN([Title])) +
       RIGHT(SPACE(5) + CAST([PageNumber] AS VARCHAR(5)), 5) AS [Output]
FROM @Contents
 
Output
------------
Introduction                                          1
Table of Contents                                     2
Index                                               100

Back to Tip of the Day List Next Tip