How to add blank into char column sql server

I have a tab开发者_开发百科le in SQL Server with a column [char](8) NOT NULL DEFAULT (' '). I've changed this column to char(21). For all existing rows I need to have blank characters from 9 to 21 position. Instead the len() is 8 and the blank characters are collapsed.

How can I resolve this problem?

Thanks

The char columns do have trailing spaces, but LEN() ignores trailing spaces, hence your misunderstanding.

See sample below:

create table char8(char8 char(8) not null constraint df_char8 default(' '), i int);
insert char8 (i) select 1
insert char8 (char8) select 't'

select char8, len(char8), len(char8+'.')-1 from char8

char8                
-------- ----------- -----------
         0           8
t        1           8

alter table char8 drop constraint df_char8
alter table char8 alter column char8 char(21)

select char8, len(char8), len(char8+'.')-1 from char8

char8                
-------- ----------- -----------
         0           21
t        1           21

Using the column+'.' makes the trailing spaces significant, so you get 9 and 22 respectively using LEN. Take one off and that is what the column contained.

You may also notice that even on display, CHAR(21) strips off trailing spaces, which shows special treatment of trailing spaces by SQL Server for char columns in at least 2 scenarios (LEN, result display in SSMS).

You could bypass the LEN() function trimming combining it with a REPLACE if you just need to have the exact length, spaces included:

LEN(REPLACE(column, ' ','*'))