Padding with zero before number to make it same length

Question

1.00/5 (1 vote)

See more:

Hi all

I have a field item_code charecter varying(40) where i have id's like 1,2,21,112 etc

Now i want to add zero to make the 1 and 2 digits code to 3 digit.

Please tell me how to do that.

Posted 4-Feb-14 17:28pm

Snehasish_Nandy

Add a Solution

Comments

Sergey Alexandrovich Kryukov 4-Feb-14 23:59pm

What's wrong with reading standard MSDN documentation?
—SA

[no name] 5-Feb-14 0:01am

I am looking for a PgSQL query..i tagged the same.i am not looking for any sql or c# thing..

Sergey Alexandrovich Kryukov 5-Feb-14 0:42am

Right, but why doing it in query. You need to work with numeric values, not string. Get numeric value and format it in your client's language. What is it?
—SA

[no name] 5-Feb-14 0:46am

i have to update the table..and i am told to run the query for this..:(

Sergey Alexandrovich Kryukov 5-Feb-14 0:54am

If you are "told to", why not asking a person who told you? :-)
—SA

[no name] 5-Feb-14 0:58am

:D..that's the problem..even they don't know..:D

Sergey Alexandrovich Kryukov 5-Feb-14 1:02am

Don't know what? The problem is trivial...
—SA

[no name] 5-Feb-14 1:06am

i know..but i only have to find the solution..

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Answer 1 · 2014-02-04T18:00:00

Solution 2

Try to find your solution here: FUNCTIONS-FORMATTING-EXAMPLES-TABLE[^]

+++++++++++++++++++++++++++++
Seeing is believing.
See for yourself my example here: http://sqlfiddle.com/#!15/45148/5[^]

I have spent much time on this. Do remember to accept the solution and vote.

Posted 4-Feb-14 18:00pm

Peter Leow

Updated 5-Feb-14 1:05am

v4

Comments

[no name] 5-Feb-14 0:09am

I tried this:
update tbl_items set item_code = RIGHT('000', 3) where len(item_code)<3
But it's giving error
function len(character varying) does not exist

Peter Leow 5-Feb-14 0:16am

Have you visited the link I provided?

[no name] 5-Feb-14 0:17am

yes..i tried that also..i got this error

function to_char(character varying) does not exist

Peter Leow 5-Feb-14 0:39am

If you have read the information provided by the link and explored the to_char() with different options, you would have found the solution yourself, try this, the output will be '001'
select to_char(1, 'FM000')

[no name] 5-Feb-14 0:44am

What you are telling is fine..but i have to update the table ..so when i am passing the column name at the place of 1 it's giving the error which i mentioned..

Peter Leow 5-Feb-14 1:36am

update tbl_items set item_code = to_char(item_code, 'FM000')

[no name] 5-Feb-14 1:49am

Same error

function to_char(character varying, unknown) does not exist

Peter Leow 5-Feb-14 2:21am

Last try, good luck:
update tbl_items set item_code = to_char(item_code::integer, 'FM000')

[no name] 5-Feb-14 2:24am

and i want to give the condition like where len(item_code) < 3
How to give this..??

Peter Leow 5-Feb-14 2:25am

first of all, did it work?

[no name] 5-Feb-14 2:27am

No...below is the error
invalid input syntax for integer: "i1"

Peter Leow 5-Feb-14 7:05am

Sigh... See amended solution 2.

[no name] 6-Feb-14 22:15pm

I think you missed one point.
the datatype for item_code is charecter varying(40) not varchar(40).

Peter Leow 6-Feb-14 22:35pm

check this out: http://stackoverflow.com/questions/1199468/what-is-the-difference-between-character-varying-and-varchar-in-postgresql