Insert leading spaces, then the value to a varchar(10) column

Question

I'm using the SQL Server database of an old program, and want to make batch inserts into the SUPPLIERS table which has its key defined as varchar(10).

The thing is that the old program fills the key with leading blanks to complete the 10 characters, example (underscore represents blankspace)

'_______310'   
'_______311'
'______1000' 

I've tried to convert the value to char(10) but it adds the blanks at the end. I've also tried to use space function, but the problem is that for key 310, I've to add 7 blanks, but for the key 1000 I need 6, so making this function dynamic on a simple select or insert statement would be very messy.

Without those spaces this app will not work properly, so do you have any idea of how to add this spaces on a simple select or insert statement?

Thanks

Answer 1

You can use the RIGHT function along with the SPACE function to dynamically calculate and insert the appropriate number of leading spaces. Here is a way to do this in your SQL statement:

INSERT INTO SUPPLIERS (key_column, other_columns)
VALUES (RIGHT(SPACE(10) + '310', 10), other_values);

For batch inserts:

INSERT INTO SUPPLIERS (key_column, other_columns)
SELECT RIGHT(SPACE(10) + CAST(key AS VARCHAR(10)), 10), other_columns
FROM (
    SELECT '310' AS key, other_value1 AS other_columns
    UNION ALL
    SELECT '311', other_value2
    UNION ALL
    SELECT '1000', other_value3
) AS batch;

RIGHT(SPACE(10) + CAST(key AS VARCHAR(10)), 10) - concatenates the 10 spaces with the key and then takes the rightmost 10 characters, ensuring the key has leading spaces.