JVMX JVMX - 5 months ago 7
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

license_number
.

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

You can use LENGTH()

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

or

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