[Webinar] Streamline your web hosting managementRegister Today


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

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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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…
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…
Free Data Recovery software is an advanced solution from Kernel Tools to recover data and files such as documents, emails, database, media and pictures, etc. It supports recovery from physical & logical drive after a hard disk crash, accidental/inte…
Suggested Courses
Course of the Month11 days, 12 hours left to enroll

640 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