How to convert a numeric value to character string padded with zeros on the left hand side

Technote (troubleshooting)


Problem(Abstract)

This document provides an example of how a numeric value can be converted to a character string and padded with zeros on the left hand side. There is no direct function to pad character strings on the left or right with a specific character.

Environment

This technique has been tested against SQL Server 2000 database. For DB2 9.7, Informix and Oracle the respective LPAD function could be used to achieve similar output. For DB2 9.5, the REPEAT function could be used to achieve similar output.

Resolving the problem

Edit the Query Subject and create a calculated column with the following definition:

replace(str (<numeric-value> ,<length>),' ',<padding-character>)

Example to pad with zeros on the left to a maximum of four characters:

replace(str ([gosales_goretailers].[Orders].[Quantity] ,4),' ','0')

As an example when using Informix: lpad( [gosales_goretailers].[Orders].[Quantity], 4, '0' )

Related information

KB 1026573: How to pad a number with zeros, convert the number

Cross reference information
Segment Product Component Platform Version Edition
Business Analytics Cognos 8 Business Intelligence Report Studio

Historical Number

1032262

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

Cognos 8 Business Intelligence
Framework Manager

Software version:

8.3, 8.4, 8.4.1

Operating system(s):

Windows

Software edition:

All Editions

Reference #:

1341868

Modified date:

2012-06-19

Translate my page

Machine Translation

Content navigation