How to Pad a column value with empty space in order to trim it down to a new size using SQL statements

Question

I have a string column in a table that currently has a data length of 30 and I'm trying to now reduce it to a length of 20. If I just reduce the length, any of the records in this column that exceeds 20 would cause an error so I would like to make sure every row has a max size of 30 then trim 10 from all of them to make sure I don't have any values that will cause issues when I resize the column (there might not be any values that break this rule but I think it would be better to handle this rather than just cross my fingers).

I need to do this using only SQL Server statements but I can't seem to find a way to do it. I'm not sure if I'm just fundamentally misunderstanding something or this isn't something I can do. I've looked at RIGHT and REPLICATE but these functions don't seem to give me the result I want.

my final goal would be something along the lines of:

update table set column = column + (empty space till we hit 30)
then trim off anything that is > 20

Any help would be appreciated or if anyone can point me in the right direction

Cheers

Answer 1

Either:

1. You want to have 20 characters in every row, and your existing values are left-aligned then padded with spaces, OR
2. You want to have no more than 20 characters in every row. If the row already has fewer than 20 characters, then leave it alone, otherwise, remove any characters that exceed the 20th character

If 1, then you could do something like this:

WITH a_table AS (
SELECT 'Facts are meaningless. You could use facts to prove anything that''s even remotely true!' + SPACE(20) as a_column
UNION
SELECT 'Come along, Bort' + SPACE(20)
)
SELECT LEFT(a_column,20) AS left_twenty, LEN(LEFT(a_column,20)) AS len_left_twenty
FROM a_table;

But note that adding 20 spaces to the right of a string that is shorter than 20 characters, then using LEFT(...,20) returns the original string (Come along, Bort). Also note that this doesn't mean that the spaces aren't there, but rather just that LEN doesn't count them. Try:

WITH a_table AS (
SELECT 'Facts are meaningless. You could use facts to prove anything that''s even remotely true!' + SPACE(20) as a_column
UNION 
SELECT 'Come along, Bort' + SPACE(20)
)
SELECT LEFT(a_column,20) AS left_twenty, LEN(LEFT(a_column,20)) AS len_left_twenty, REPLACE(LEFT(a_column,20),' ','#') AS left_twenty_use_octothorpe
FROM a_table;

If 2, which I think is much more likely, then you just want this (i.e. the same as above, but without all that SPACE nonsense):

WITH a_table AS (
SELECT 'Facts are meaningless. You could use facts to prove anything that''s even remotely true!' AS a_column
UNION 
SELECT 'Come along, Bort'
)
SELECT LEFT(a_column,20) AS left_twenty, LEN(LEFT(a_column,20)) AS len_left_twenty, REPLACE(LEFT(a_column,20),' ','#') AS left_twenty_use_octothorpe
FROM a_table;