CODEWITHSUNDEEP
sqlsql-serversql-server-2012
user1949329
user1949329

Reputation: 207

Add specific column length padding to an already populated column

I have a table column filled with values. The Column is set to Varchar(100). The problem I have is that I need to take whatever data is there and expand it out with "Padding" until each one is 100 characters.

All of this goes into a fixed width flat file.

Each entry in the column is between 20 and 30 characters right now. The padding I will be adding is "blank spaces"

My problem is that I am not sure how to write an update statement to handle updating the existing data. I have already fixed the future data.

Any suggestions?

Upvotes: 0

Views: 458

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can do this with an update. The simplest method, though, is probably to change the type to char(100):

alter table t alter column col char(100);

char() pads values with spaces on the right.

Alternatively, you can just do:

update t
    set col = col + replicate(' ', 100);

I don't think SQL Server complains about the size difference. If you are concerned about a potential error, you can do:

update t
    set col = col + replicate(' ', 100 - len(col));

Upvotes: 4

Related Questions