RPAD (SQL)

A string function that returns a string right-padded to a specified length.

Synopsis

RPAD(string-expression,length[,padstring])

Description

RPAD pads a string expression with trailing pad characters. It returns a copy of the string padded to length number of characters. If the string expression is longer than length number of characters, the return string is truncated to length number of characters.

If string-expression is NULL, RPAD returns NULL. If string-expression is the empty string ('') RPAD returns a string consisting entirely of pad characters. The returned string is type VARCHAR.

RPAD can be used in queries against a linked table.

RPAD does not remove leading or trailing blanks; it pads the string including any leading or trailing blanks. To remove leading or trailing blanks before padding a string, use LTRIM, RTRIM, or TRIM.

Arguments

string-expression

A string expression, which can be the name of a column, a string literal, a host variable, or the result of another scalar function. Can be of any data type convertible to a VARCHAR data type. string-expression cannot be a stream.

length

An integer specifying the number of characters in the returned string.

padstring

An optional string consisting of a character or a string of characters used to pad the input string-expression. The padstring character or characters are appended to the right of string-expression to supply as many characters as need to create an output string of length characters. padstring may be a string literal, a column, a host variable, or the result of another scalar function. If omitted, the default is a blank space character.

Examples

The following example right pads column values with ^ characters (when needed) to return strings of length 16. Note that some Name strings are right padded, some Name strings are right truncated to return strings of length 16.

   SELECT TOP 15 Name,RPAD(Name,16,'^') AS Name16
   FROM Sample.Person

The following example right pads column values with the ^=^ pad string (when needed) to return strings of length 20. Note that the pad name string is repeated as many times as needed, and that some return strings contain partial pad strings:

   SELECT TOP 15 Name,RPAD(Name,20,'^=^') AS Name20
   FROM Sample.Person