LPAD in SQL Server 2008

Question

I can't see a function like LPAD in SQL Server 2008. For example how can I convert the following queries into T-SQL?

select  LPAD(MY_VALUE,2,' ')) VALUE FROM MY_TABLE 

Answer 1

Basically pad it with the number of characters you are intending to select and then right the string.

Select right(replicate(' ',2) + YourFieldValue,2) from YourTable 

You can use the space function instead of replicate, space(number_of_spaces), replicate just allows you to pad with alternative characters.

Answer 2

Manual calculations can be annoying to apply inside queries. Luckily, we can create a function:

CREATE FUNCTION LPAD (     @string VARCHAR(MAX), -- Initial string     @length INT,          -- Size of final string     @pad CHAR             -- Pad character ) RETURNS VARCHAR(MAX) AS BEGIN     RETURN REPLICATE(@pad, @length - LEN(@string)) + @string; END GO 

(Please replace VARCHAR with NVARCHAR to your liking, or use an alternative algorithm.)

Then:

SELECT dbo.LPAD(MY_VALUE, 2, ' ') VALUE FROM MY_TABLE 

Should work since SQL Server 2005.