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

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 v开发者_StackOverflow中文版alues

.

5  must become 005
89 must become 089

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

thanks tksy

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

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

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]'
         );

Create --> Query Design --> Update Query

Select the variable you want to update, then in the update to field, type:

Format([variable name],"0000000")

I used 7 zeros above because I wanted the to fill in zeros in the beginning of a number string up to 7 places total.

Note that this can be used only for a text field.

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)

ACCESS

SELECT FORMAT(startTable.numField,"00#") AS txtField
FROM startTable
WHERE startTable.numField IS NOT NULL;

...

UPDATE destinationTable
SET destinationTable.txtField = FORMAT(startTable.numField,"00#")
WHERE startTable.numField IS NOT NULL;

...

INSERT INTO destinationTable ( txtField )
SELECT FORMAT(startTable.numField,"00#") AS txtField
FROM startTable
WHERE startTable.numField IS NOT NULL;

SQL

SELECT RIGHT('000' + CAST(numField AS VARCHAR(3)), 3) AS txtField
FROM startTable
WHERE startTable.numField IS NOT NULL

...

UPDATE destinationTable
SET destinationTable.txtField = RIGHT('000' + CAST(startTable.numField AS VARCHAR(3)), 3)
WHERE startTable.numField IS NOT NULL

...

INSERT INTO destinationTable ( txtField )
SELECT RIGHT('000' + CAST(numField AS VARCHAR(3)), 3) AS txtField
FROM startTable
WHERE startTable.numField IS NOT NULL