If you need to ignore the suggestions of Bob Hovious and Jeff Moden look at this code and decide what you want to do.

CREATE TABLE #Demo(Col1 VARCHAR(9),Col2 VARCHAR(9))

DECLARE @Blanks VARCHAR(9)

DECLARE @Value VARCHAR(9)

SET @Value = '123'

SET @Blanks = ' '

INSERT INTO #Demo

VALUES (RIGHT(@Blanks + @Value,9),@Value)

SELECT Col1 AS 'this is what I want',LEN(Col1) AS 'Lenght to prove what I want has leading blanks',RIGHT(' ' + Col2,9) AS 'Space saving'

,LEN(RIGHT(' ' + Col2,9))AS 'Prove space savings has leading blanks',Col2 AS 'Recommended by Bob Hovious'

FROM #Demo

DROP TABLE #Demo