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

more ▼

asked Jan 12 at 03:26 PM in Default

avatar image


What is the original datatype for the ADM.PATIENT_NUMBER column?

Jan 15 at 04:38 AM Jeff Moden
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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:

     replace(str(ADM.PATIENT_NUMBER, 10), ' ', '0') as FormattedNumber
     from YourTable;

Hope this helps.


more ▼

answered Jan 12 at 03:35 PM

avatar image

20.6k 3 7 29

Thanks Oleg !! Great idea and nice and clean.

Jan 12 at 03:52 PM snwskr
(comments are locked)
10|1200 characters needed characters left

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  
     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)

more ▼

answered Jan 12 at 03:40 PM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

@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.

Jan 12 at 03:55 PM Oleg

That would explain it. Kind of why I asked what the datatype is. Thanks @Oleg

Jan 12 at 04:00 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 12 at 03:26 PM

Seen: 54 times

Last Updated: Jan 15 at 04:38 AM

Copyright 2018 Redgate Software. Privacy Policy