SQL Assistance Needed

[bi4u2]

I am trying to figure a way to pad a check number with zeros up to 18 digits in the field. For instance if the check number is 4 digits, I need to add '00000000000000' (14 zeros). I set up a case statement to count the number of characters and add back zeros, but my solution requires that I layout each and every possibility. Is there anyway to achieve this in SQL?

This is what I have so far, just a handful of the 18 possibilites:

cast(CHK_NO as varchar (10))  as  Check_Number,
       case (18 - char_length(cast(CHK_NO as varchar (10))))
         when 11 then '00000000000'
         when 12 then '000000000000'
         when 13 then '0000000000000'
         when 14 then '00000000000000'
         when 15 then '000000000000000'
         when 16 then '0000000000000000'
         else ''
       end

[blom0344]

just a handful of the 18 possibilites

6 done , 12 to go..

Takes about 30 seconds to copy / paste the remainder..

You are almost there!!!   ;D

[bi4u2]

Thanks, I know I was almost there, just wanted to improve my skills if there was a better way.

[Lynn]

There is a better way!

Concatenate 18 zeros in front of the check number and then take the 18 rightmost characters. This is easiest to do if your database supports a "right" function. If not, you need to do a substring with a calculation to compute the starting position. The computation for that would be the length of the concatenated string minus 18 plus 1.

Code Select Expand


right( '000000000000000000' || cast(CHK_NO as varchar (10)) , 18 )


[bi4u2]

Thanks Lynn, yes our SQL Server supports 'RIGHT' - this is a real timesaver!

[blom0344]

Lynn,

Cool solution!!   ;)