JVMX JVMX - 1 year ago 55
SQL Question

Appending a "0" to the front of a string

Someone in my company downloaded some data, played with it in Excel and uploaded again.

Excel when trying to be helpful truncated a leading zero on a file called


As a result rather than having "037463524" the data now says "37463524"

I know that if the string is eight characters long, I need to add a "0" to the front of it to correct the mess.

Is there a SQL query that I can run in order to accomplish this?

Answer Source

You can use LENGTH()

UPDATE Tablename SET license_number = '0' + license_number WHERE LENGTH(license_number) = 8


UPDATE Tablename SET license_number = CONCAT('0', license_number) WHERE LENGTH(license_number) = 8
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download