Adding Leading Zeros to a string
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
Software/Hardware used:
ACCESS
ASKED:
May 18, 2010 6:45 PM
UPDATED:
May 18, 2010 10:54 PM
Answer Wiki:
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>
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
