CODEWITHSUNDEEP
mysql
JVMX
JVMX

Reputation: 1078

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?

Upvotes: 1

Views: 70

Answers (2)

JVMX
JVMX

Reputation: 1078

One more way by using LPAD

UPDATE `TABLE` SET `Lic_NO` = LPAD(`Lic_NO`, 9, '0')

Upvotes: 1

Arulkumar
Arulkumar

Reputation: 13237

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

Upvotes: 5

Related Questions