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 GMT
UPDATED:
May 18, 2010 10:54:53 PM GMT
RELATED QUESTIONS
- %char(month_field) yields just '1' and not '01'
- CPYTOIMPF
- How to convert characters to numeric in CSV file?
- Trimming just one leading zero (if present) in SQLRPGLE
- Numeric to character conversion in AS/400
- How to identify whether the field values are in particular format
- AS/400 query
- Need help with FILEAID in BATCH
- converting numeric field to a character field and show leading zeros
- Difference between two strings using CL/400, AS/400, COBOL/400
Answer Wiki:
Here are a couple of options:
You could create a function to parameterize the padding:
or
And then use this function in your queries:
UPDATE yourTable
SET yourField = STRING(9-LEN(yourField),'0') & yourField
WHERE LEN(yourField) < 9;
UPDATE yourTable
SET yourField = RIGHT(STRING(9, '0') & yourField, 9)
WHERE LEN(yourField) < 9;
You could create a function to parameterize the padding:
Public Function lPad(strIn, n As Integer, strPad As String) As String
lPad = STRING(n-LEN(strIn),strPad) & strIn
End Function
or
Public Function lPad(strIn, n As Integer, strPad As String) As String
lPad = RIGHT(STRING(n, strPad) & strIn, n)
End Function
And then use this function in your queries:
UPDATE yourTable
SET yourField = lPad(yourField,9,'0')
WHERE LEN(yourField) < 9;
60,245 pts.
To see other answers submitted to the Answer Wiki View Answer History.
