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.
Rule Type 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’