asked
How do I format or zero fill a 7 digit field to 10 digits
I don't know why I am having so much trouble finding this. I am using SS 2008. I am trying to take a patient number (which is the primary key with a length of 7 digits) and output a 10 character field. I tried REPLICATE('0', 10 - len(ADM.PATIENT_NUMBER)) + CAST(ADM.PATIENT_NUMBER as char(10)), That works fine when the number is 7 digits. 1677766 outputs as 0001677766 But when the number is less than 7 digits it fails. 400793 outputs as 000 400793 98317 outputs as 000 98317 Please help Thanks, Paul
One the easy ways of getting the results you need is via str function. This function simply "right aligns" the input padding it with as many spaces as necessary (and only if necessary). Once applied, the replace can be applied to simply replace the spaces with zeroes. This way, the method will always work regardless of how many digits the column value has. Here is the script: select replace(str(ADM.PATIENT_NUMBER, 10), ' ', '0') as FormattedNumber from YourTable; Hope this helps. Oleg
I would use the RIGHT function to return the last 10 chars of a padded value, but when I compare it to your solution I get the same (correct) results. What is the datatype of ADM.PATIENT_NUMBER? declare @PatientNumbers table (PatientNumber int) insert into @PatientNumbers (PatientNumber) select 1677766 insert into @PatientNumbers (PatientNumber) select 400793 insert into @PatientNumbers (PatientNumber) select 98317 select replicate('0', 10 - len(PatientNumber)) + CAST(PatientNumber as char(10)), right('0000000000'+cast(PatientNumber as varchar(7)),10) from @PatientNumbers ------------ ---------- 0001677766 0001677766 0000400793 0000400793 0000098317 0000098317 (3 rows affected)
@Kev Riley ♦♦ The problem with OP data is that some values are probably left padded, thus retaining the "hole" between the zeroes and the numbers. Additionally, the OP method causes way too many spaces on the right hand side, courtesy of ***CAST(PatientNumber as char(10))*** part. This is why replace and str combination work regardless of any spaces on either side of the numbers (if any), and regardless of whether the column is char or varchar. Even the solution with RIGHT function will still require the ltrim to be applied first.
That would explain it. Kind of why I asked what the datatype is. Thanks @Oleg
19 People are following this question.
