LPAD in SQL Server 2008

问题:

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

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

回答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.

回答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.

回答3:

I needed something similar but I couldn't use '+' because then it was interpreted as an addition between the numbers '0000000000' and [seq_no]. So I used concat instead and it worked fine.

select right (concat(replicate('0', 10),  next value for seq_no), 10);

回答4:

I've come up with a LPAD and RPAD function where you can use a characterstring for the pad-part.

They should work the same as the DB2 versions.

Here's the LPAD:

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

And here is the RPAD:

CREATE FUNCTION dbo.RPAD
(
    @string NVARCHAR(MAX), -- Initial string
    @length INT,           -- Size of final string
    @pad NVARCHAR(MAX)     -- Pad string
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    RETURN @string + SUBSTRING(REPLICATE(@pad, @length),1,@length - LEN(@string));
END
GO