Padding field in SELECT query

Posted on 2003-02-25
Medium Priority
Last Modified: 2011-05-30
I want to pad the fields while selecting them in the query. The query should look like -

SELECT PAD(<field name>,10,"0") from <table name>
where <condition>

So if the value of the files is 12345 the query should return 0000012345. Also what would be the option if I want to right-pad ?

Is there a function for this in DB2 UDB

Question by:Sudven
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

mglxxx earned 80 total points
ID: 8024249
Left pad:
repeat('0', <format_length> - length(char(<field_name>))) || char(<field_name>)

Right pad:
char(char(<field_name>) || repeat('0', <format_length>)), <format_length>)

This is a little bit kludgy but AFAIK there's nothing in
DB2 which can do this directly.
LVL 13

Expert Comment

ID: 8034017
I think an alternate way may be:
select concat('00000', cast(field name as char(5)) from tn


Author Comment

ID: 8034150
This worked for me. Although I had to use rtrim to trim the fields before they could be padded because using
char() padded spaces to the values.
So my syntax looked like -
REPEAT('0', 4-LENGTH(RTRIM(CHAR(<field name>)))) || RTRIM(char(SITE_CD))

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question