Netezza Pad Zero with Working Example

Netezza pad zero functions are string functions used to remove or add the string to beginning or end of the expression. An expression could be column values.

There are two pad functions available in Netezza; LPAD and RPAD string functions. You can use those function in Netezza pad zero or any other character provided in arguments:

Read:

• Netezza String Functions and Usage
• Different types of Netezza Trim Functions and Examples
• Netezza Date Functions and Examples
• nzsql Command and its Usage

Netezza LPAD function

You can use this function to pad string on the left. There is an optional third argument that specifies the pad character. You can provide this as “0” if you want to perform Netezza pad zero to left side.

Netezza LPAD function Syntax:

lpad ( string_expression1 , integer_expression [ , string_expression2 ] )

Returns “string_expression1” left padded to length “integer_expression” with occurrences of “string_expression2”. If “string_expression1” is longer than “integer_expression”, the appropriate portion of “string_expression1” is returned.

Netezza RPAD function

You can use this function to pad string on the right. There is an optional third argument that specifies the pad character. You can provide this as “0” if you want to perform Netezza pad zero to right side.

Netezza LPAD function Syntax:

rpad ( string_expression1 , integer_expression [ , string_expression2 ] )

Returns “string_expression1” right padded to length “integer_expression” with occurrences of “string_expression2”. If “string_expression1” is longer than “integer_expression”, the appropriate portion of “string_expression1” is returned. If “string_expression2” is not specified, then spaces are used.

Netezza LPAD and RPAD Examples

Netezza padding zero to left of the string

SELECT LPAD(a.sample, 10, 0) as row_res 
FROM (SELECT 12345 as sample) a;
 ROW_RES 
------------
 0000012345
(1 row)

Netezza pad zero to right of the string

SELECT RPAD(a.sample, 10, 0) as row_res 
FROM (SELECT '12345.' as sample) a;
 ROW_RES 
------------
 12345.0000
(1 row)

Remove leading zero – left padded zeros

SELECT CAST(TRIM(Leading '0' FROM a.sample) as integer) as row_res
FROM (SELECT '0000012345' as sample) a;
 ROW_RES 
---------
 12345
(1 row)

Netezza pad zero without using LPAD function

You can append the 0 to result without using LPAD function:

SELECT TO_CHAR(12345, 'fm0000000000');
 TO_CHAR 
------------
 0000012345
(1 row)