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?

@maSTAShuFu Legacy app using Sql Server for generate CSV file (with blank an spaces format) for extern company. Legacy app — Kiquenet
– Kiquenet, Commented Nov 9, 2017 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
– Martin Smith, Commented Nov 9, 2017 at 23:17
@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
– RoMEoMusTDiE, Commented Nov 9, 2017 at 23:22
@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
– Brian, Commented Nov 10, 2017 at 2:57

Mathew Paxinos · Accepted Answer · 2017-11-10 00:07:15Z

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

Ilyes · Accepted Answer · 2017-11-09 23:57:56Z

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

Collectives™ on Stack Overflow

Fill with spaces a column value in update

2 Answers 2

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Linked

Related