Adding Leading Zeros to a string

5 pts.
Tags:
Microsoft Access
String manipulation
I have a field with a mixture of 9 and 8 character strings... I need to make them all 9 characters in length..so how do I add a leading zero to those records with only 8 characters while leaving those records with 9 characters alone?

Software/Hardware used:
ACCESS

Answer Wiki

Thanks. We'll let you know when a new response is added.

Here are a couple of options:

<pre>UPDATE yourTable
SET yourField = STRING(9-LEN(yourField),’0′) & yourField
WHERE LEN(yourField) < 9;</pre>

<pre>UPDATE yourTable
SET yourField = RIGHT(STRING(9, ’0′) & yourField, 9)
WHERE LEN(yourField) < 9;</pre>

You could create a function to parameterize the padding:

<pre>Public Function lPad(strIn, n As Integer, strPad As String) As String
lPad = STRING(n-LEN(strIn),strPad) & strIn
End Function</pre>

or

<pre>Public Function lPad(strIn, n As Integer, strPad As String) As String
lPad = RIGHT(STRING(n, strPad) & strIn, n)
End Function</pre>

And then use this function in your queries:

<pre>UPDATE yourTable
SET yourField = lPad(yourField,9,’0′)
WHERE LEN(yourField) < 9;</pre>

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following