SQL Server Query - Paste @space with spacebar

Question

I am currently doing a stored procedure with a parameter when I insert data by typing a space as shown below with the replicated format to make the result.

declare @space varchar(200) select @space = replicate(' ',200) select Len(@C) 

Unfortunately, the result is 0 when executing the query

 @space ------- 0 

but typing a instead of a space works because len from @space has 200.

Is there any idea how to make @space len to 200 using space as data?

 @space ------ 200 

Answer 1

See DATALENGTH (Transact-SQL)

 declare @space varchar(200) select @space = replicate(' ',200) select Len(@space),DATALENGTH(@space) 

SQL Fiddle DEMO

From LEN (Transact-SQL)

Returns the number of characters of the specified string expression, excluding trailing spaces .

Answer 2

Try this option -

 DECLARE @space VARCHAR(200) SELECT @space = REPLICATE(' ', 200) SELECT LEN(@space + '_') - 1 SELECT DATALENGTH(@space)