Post
Ask Question

Space Function in SQL

SPACE function in SQL Server accepts integers as input parameters. Depending upon the value of the parameter passed, the same number of spaces as the parameter passed are generated.

Let us check the same with the help of some examples.

  1. DECLARE @Student TABLE  
  2. (  
  3.    StudentIdint NOT NULL,  
  4.    FirstNamevarchar(20),  
  5.    LastNamevarchar(20),  
  6.    Marks int  
  7. )  
  8.   
  9. Insert Into @Student (StudentID,FirstName,LastName,Marks) Values (1,'John','Smith',200)  
  10. Insert Into @Student (StudentID,FirstName,LastName,Marks) Values (2,'Frank','Doe',400)  
  11.   
  12. SELECT RTRIM(FirstName) + SPACE(10) + LTRIM(LastName) as Name  
  13. FROM @Student  
In the preceding example we have a table variable with a few columns like StudentID, Marks, FirstName and LastName. We want to display the FirstName concatenated with lastName but what we also want is to provide a certain gap between these columns in the output. Space function comes in handy when we need to replicate the above scenario. Let us see the output of the below commands.



Using space function we have created space between the two concatenated values. Passing the parameter a value of 10 gives 10 spaces between the two columns.

This is how we can use Space function in SQL Server.