We can use SPACE function to replicate spaces for a string. Consider the following statement
select 'A'+space(10)+'B'
The result is
------------ A B
As you see space function adds 10 spaces between the string A and B. Note that the space function though accepts any positive number, it will return maximum of 8000 spaces only which you can confirm from the following example
select datalength(space(10000))
The above returns 8000. So you should be aware of this. The alternate solution to have more spaces is to use replicate function as shown below
select datalength(replicate(cast(' ' as varchar(max)),10000))
Which returns 10000. Also note that replicate returns maximum 8000 characters so we need to convert the string to varchar(max) datatype to have more chanracters. Find out more details at Varchar(max) datatype and replicate function
Tags: sql_server, replicate, sqlserver, SQL Server, #SQL SERVER, space,
I enjoyed reading the document. But couldn't get, why it is behaving like so? Can u plz explain it in more specific.
Thanks in advance.
Becuase SPACE and REPLICATE functions by default will return only 8000 characters