2

I have a query where I'm pulling in a check number from the database. The return value needs to always be 9 characters exactly. Most of the check numbers are only 6 characters, so I want to prepend 0s to to the string to ensure it is always exactly 9 characters. Since the check numbers can vary in length, it will be a different number of 0s for each one. What's the most efficient way to do this?

Jana
  • 61
  • 1
  • 3
  • 9

1 Answers1

0

If they're always between 6 and 9 digits, the easiest (and probably best) way to go is using CASE.

SELECT CASE WHEN LEN(CHECK_NUM)= 6 THEN '000' WHEN LEN(CHECK_NUM) = 7 THEN '00' 
       WHEN 
       LEN(CHECK_NUM) = 8 THEN '0' ELSE '' END + CHECK_NUM 
FROM   TABLE

EDIT
In case the original values are numbers (int), try something like this:

SELECT CASE WHEN LEN(CHECK_NUM)= 6 THEN '000' WHEN LEN(CHECK_NUM) = 7 THEN '00' 
       WHEN 
       LEN(CHECK_NUM) = 8 THEN '0' ELSE '' END + CAST(CHECK_NUM AS VARCHAR(9)) 
FROM   TABLE1

Take a look at the this SQL Fiddle.

Gidil
  • 4,137
  • 2
  • 34
  • 50
  • Thanks for the suggestion - I've tried it, however, it doesn't appear to be actually adding the zeros to the beginning: select case when LEN(cr.CHECK_NUM_NUMERIC) = 6 then '000' when LEN(cr.CHECK_NUM_NUMERIC) = 7 then '00' when LEN(cr.CHECK_NUM_NUMERIC) = 8 then '0' else '' end + cr.CHECK_NUM_NUMERIC from chk_reconciliation cr – Jana Sep 04 '13 at 14:23
  • Is the field a int or a varchar? – Gidil Sep 04 '13 at 14:25