Pad Field Value

Sometimes you want to pad out a fields value by adding something at the front of the data in that field.

For example you have some numbers that are codes and they start at 1 and go to 151. But you need the codes to be 3 digits long so rather than 7 you need the number to be formatted as 007.

Pattern Format

lpad(field name , how many characters you want , what character to pad it with)

rpad(field name , how many characters you want , what character to pad it with)

Snippits

Pad the left - Text Value

Pad the left of the current value with a character.

Rule Type = Calculated Output Field

Rule Attribute = lpad(FieldName , 5 , x)

Examples

  • abc becomes xxabc

  • y becomes xxxxy

Pad the left - Number Value

Pad the left of the current value with a number.

Rule Type = Calculated Output Field

Rule Attribute = lpad(FieldName , 3 , 1)

Examples

  • 9 become 119

  • 99 become 199

Pad the right - Text Value

Pad the right of the current value with a character.

Rule Type = Calculated Output Field

Rule Attribute = rpad(FieldName , 5 , x)

Examples

  • abc becomes abcxx

  • y becomes yxxxx

Pad the right - Number Value

Pad the right of the current value with a number

Rule Type = Calculated Output Field

Rule Attribute = lpad(FieldName , 3 , 1)

Examples

  • 9 become 900

  • 99 become 991

Pad the left or right with blank spaces

If you want to pad the field with spaces you do not need to define the character to pad it with, it will use a blank space by default.

Rule Type = Calculated Output Field

Rule Attribute = lpad(FieldName , 5)

Rule Attribute = rpad(FieldName , 5)

Examples

  • abc becomes ‘ abc’

  • abc becomes ‘abc ‘

Pad a number on the left with zeros

If you pad a number on the left with zero’s and then import the data into a tool such as excel, excel will drop the leading zero’s. So you need to convert the field to be a string not a number so that excel will retain the leading zero’s.

Remember to make the padding character a sting value as well so ‘0’ not 0.

Rule Type = Calculated Output Field

Rule Attribute = lpad(cast(FieldName as string),3,’0’)

Examples

  • 9 becomes ‘009’

  • 129 becomes ‘129’