CODEWITHSUNDEEP
sql-serversql-server-2012spacesnvarchar
Kiquenet
Kiquenet

Reputation: 14996

Fill with spaces a column value in update

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?

Upvotes: 0

Views: 4915

Answers (2)

Mathew Paxinos
Mathew Paxinos

Reputation: 964

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

Upvotes: 3

Ilyes
Ilyes

Reputation: 14928

I don't understand what you want exactly, but here is what I understand:

CREATE TABLE MyTable (
    Col1 NVARCHAR(200),
    Col2 NVARCHAR(100),
    Col3 NVARCHAR(200),
    Col4 NVARCHAR(50)
    );
    INSERT INTO MyTable VALUES (NULL, NULL, NULL, NULL), ('ABC', NULL, NULL, NULL);
-- You can do the same for the other cols
        UPDATE MyTABLE 
        SET Col1 = REPLICATE(' ', COL_LENGTH('MyTable', 'Col1')/2)
        WHERE Col1 IS NULL;

SELECT *
FROM MyTable;

Demo

Update:

Here is how to do it in one statement:

UPDATE MyTABLE 
SET Col1 = (SELECT CASE WHEN (Col1 IS NULL) OR (Col1 = '') THEN 
                REPLICATE(' ', COL_LENGTH('MyTable', 'Col1')/2) 
                ELSE Col1 + REPLICATE(' ', (COL_LENGTH('MyTable', 'Col1')/2)- LEN(Col1)) END),
Col2 = (SELECT CASE WHEN (Col2 IS NULL) OR (Col2 = '') THEN 
                REPLICATE(' ', COL_LENGTH('MyTable', 'Col2')/2) 
                ELSE Col2 + REPLICATE(' ', (COL_LENGTH('MyTable', 'Col2')/2)- LEN(Col2)) END),                
Col3 = (SELECT CASE WHEN (Col3 IS NULL) OR (Col3 = '') THEN 
                REPLICATE(' ', COL_LENGTH('MyTable', 'Col1')/2) 
                ELSE Col3 + REPLICATE(' ', (COL_LENGTH('MyTable', 'Col3')/2)- LEN(Col3)) END), 
Col4 = (SELECT CASE WHEN (Col4 IS NULL) OR (Col4 = '') THEN 
                REPLICATE(' ', COL_LENGTH('MyTable', 'Col4')/2) 
                ELSE Col4 + REPLICATE(' ', (COL_LENGTH('MyTable', 'Col4')/2)- LEN(Col4)) END);

Demo

Upvotes: 1

Related Questions