SQL Server Performance

LPAD function?

Discussion in 'General Developer Questions' started by rweinstein, Jun 29, 2005.

  1. rweinstein New Member

    I see in some documentation on the web that there is an "LPAD" function:

    LPAD(string, length, pad), RPAD(string, length, pad)
    Returns string padded on left or right to length characters using the pad string as padding. Pad may be omitted: defaults to single space.

    I do not see that this exists in SQL Server, does anyone know how I can do this in SQL Server without having to use a ridiculous case statement?

    I have an incoming field that is 2, 3, 4, or up characters and I want to deliver this to a field with size of 12, with the data "right-justified" and padded will spaces or nulls at the beginning.

    For example:

    incoming Data: "ABC"
    outgoing data: " ABC"

    Thanks.

    Rodney
  2. FrankKalis Moderator

    You don't need a CASE expression for this


    DECLARE @a VARCHAR(3)
    SET @a = 'ABC'
    SELECT
    RIGHT(REPLICATE(' ',10) + @a ,10) AS RPAD
    , LEFT(@a + REPLICATE(' ',10) ,10) AS LPAD

    RPAD LPAD
    ---------- ----------
    ABC ABC

    (1 row(s) affected)



    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  3. rweinstein New Member

    Frank,

    As always, thank you very much. I really appreciate the rapid turn around and the quick solution.

    It worked like a charm! I didn't know about the REPLICATE statement. I searched through books online for "pad" to no avail.

    Thanks!

    Rodney

Share This Page