Zipcodes missing digit

[clay1]

I have a bunch of entries where the zipcode is missing the first digit

 

Besides manually editing all of them is there a query I can write that will add it back?

 

For example I've got

 

xxxx

 

I want it to be 0xxxx

[PFMaBiSmAd]

Zipcodes are not actually numbers. They are formatted strings consisting of numeric digits. As soon as you store them as a number they will loose any leading zeros unless you define them as having leading zeros.

 

What is your column definition?

[clay1]

My table they are txt

 

They are first being stored in a vendors table as varchar

[PFMaBiSmAd]

You must be referencing them as a number at some point, rather than as a string. When you look directly in the database table as the leading zero's present in the data or does this problem show up when you display the values?

 

You can correct the existing data, but until you find at what point during the processing the leading zeros get dropped, any new data will have the same problem.

 

The simplest solution would be to always left pad the string with zero's, either at the point they are inserted or at the point where you display them.

[clay1]

I am looking directly in the table.

 

Not all leading 0s are missing.. The issue is older data the vendor has sent me.. I just need to fix it and I would prefer not needing to manually edit each one.

 

Can I use a regex to match the zip codes that have 4 digits and add the zero?

[PFMaBiSmAd]

Can I use a regex to match the zip codes that have 4 digits and add the zero?

 

Yes or use LENGTH()

[clay1]

Using length how would I prepend the 0?

 

Thanks

[PFMaBiSmAd]

Untested but should work -

 

UPDATE your_table SET zip_column = CONCAT('0', zip_column) WHERE LENGTH(zip_column) = 4