0

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?

Kiquenet
  • 14,494
  • 35
  • 148
  • 243
  • 1
    @maSTAShuFu ***Legacy*** app using Sql Server for generate CSV file (with blank an spaces format) for extern company. ***Legacy app*** – Kiquenet Nov 09 '17 at 23:17
  • You would get the behaviour you want if you used nchar instead of nvarchar. But **do not do this**. As it would be a highly inefficient use of resources. – Martin Smith Nov 09 '17 at 23:17
  • 1
    @MartinSmith not possible, ***Legacy app*** – Kiquenet Nov 09 '17 at 23:18
  • @Kiquenet, how do you transfer the Data from CSV to sql? SSIS should allow you to transform your data. you can apply trim on the fly. I don't see why you can't. If manual, you can still trim the CSV fields. – RoMEoMusTDiE Nov 09 '17 at 23:22
  • 1
    @Kiquenet, why does your _**Legacy app**_ care how the data is stored in the database? Add the spaces as needed when you query the data. – Brian Nov 10 '17 at 02:57
  • @maSTAShuFu legacy App, not time to changes to SSIS. Transfer CSV to SQL Server is a Windows App Legacy, and the same App generate new CSV from SQL Server. – Kiquenet Nov 10 '17 at 07:17

2 Answers2

3

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
Mathew Paxinos
  • 944
  • 7
  • 16
1

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

Ilyes
  • 14,640
  • 4
  • 29
  • 55