onur onur - 4 months ago 23
SQL Question

postgresql update row to fixed digit number

I am beginner in sql query and I am trying to update my rows like that:

1--->0001

15-->0015

254-->0254

1458-->1458

My column's type is text and there are lots of columns so I cannot handle with

update table1 set col1 = 0001 where col1 = 1;


and so on..

This seems easy question but after research,I could not find a solution. all I need is something like

foreach row in col1
if((int)row>0 and < 10)
then row = "000" + row;


All texts are infact integer value but I have to keep them as text. Whats sql query of above code?

Thanks

Answer

You can use the lpad() function:

update table1
   set col1 = lpad(col1, 4, '0')
where length(col1) < 4;

But the real question is: why are you storing numbers as text values? That is almost always a bad choice.