Fill with spaces a column value in update

Question

How to automatically fill a column with spaces to a pre-determined length in update SQL sentence in SQL Server 2012?

I have a table with several columns like

Col1 NVARCHAR(10)
Col2 NVARCHAR(100)
Col3 NVARCHAR(200)
Col4 NVARCHAR(50) 

and more.

If value of column is NULL or '', I update the column with spaces to a pre-determined length (the lenth of the column).

For Col3, if value is NULL or '', spaces to 200 blank space (' ') if value has any characters, 'abcd', fill (pad right) to 200 blank spaces. Then, finally 4 not spaces characters and 196 spaces characteres.

For example, for Col1 has length 10.

1) Value = NULL    , Col1 value = '         ' (10 spaces)
2) Value = ''      , Col1 value = '         ' (10 spaces)
2) Value = 'abc'   , Col1 value = 'abc      ' (abc and 7 spaces)

How can I do that in the UPDATE SQL?

Maybe using

select column_name, data_type, character_maximum_length    
  from information_schema.columns  
 where table_name = 'myTable'

or

SELECT COL_LENGTH('Table', 'Column')

More in How to get the size of a varchar[n] field in one SQL statement?

Answer 1

Try the following, the LEFT is used to keep the length down to the column length, while the space ensures the field is filled with spaces:

create table test (col1 varchar(10), col2 varchar(15))
GO

insert into test (col1, col2)
    values   ('', '')
            ,(NULL, NULL)
            ,('abc', 'abc')

UPDATE test
    SET  col1 = LEFT(COALESCE(col1, '') + SPACE(COL_LENGTH('test', 'col1')), COL_LENGTH('test', 'col1'))
        ,col2 = LEFT(COALESCE(col2, '') + SPACE(COL_LENGTH('test', 'col2')), COL_LENGTH('test', 'col2'))
FROM    test

SELECT  *
FROM    test