How to add zeros in front of a text field in an access table.

Question

Hi,

I have an access table with a text field. It has alpha numeric values. But i want all values to be minimum 3 digit. so i want to add zeroes in front of all single or two digit values

.

5  must become 005
89 must become 089

how do i write a query to update all values in the table.

thanks tksy

Answer 1

In general, you can prefix 3 zeros to the front and then substring out the last 3 digits from the right. Something like right("000" + val, 3) if those were real function.

I think in Access it is MID("000" & val, 3, 3)

Answer 2

update tbl
set Col1 = RIGHT("000" + Col1,3)
where len(Col1) < 3

Answer 3

The key is to add as many zeros as needed to take the length up to 3.

UPDATE yourTable
SET YourField = LEFT("00", 3-LEN(YourField)) + YourField
WHERE LEN(YourField)<3 AND Len(YourField)>0

Answer 4

While its usually important to try to do as much as possible in one SQL statement (to help the optimizer for OLTP applications etc), this appears to be a data corruption scenario so I assume a one-off data scrubbing exercise is all that's required. And when you consider there's only two cases ("single or two digit values") there's no real harm in using two UPDATE statements e.g.

UPDATE Test1
   SET text_col = '0' + text_col
 WHERE text_col ALIKE '[0-9][0-9]';

UPDATE Test1
   SET text_col = '00' + text_col
 WHERE text_col ALIKE '[0-9]';

HOWEVER, the most important part of the exercise is to apply data constraints to ensure the data corruption doesn't reoccur e.g. (ANSI-92 SQL Mode syntax):

ALTER TABLE Test1 ADD
   CONSTRAINT text_col__numeric__at_least_three_digits
   CHECK (
          text_col NOT ALIKE '[0-9]'
          AND text_col NOT ALIKE '[0-9][0-9]'
         );