How do I Zero Fill a SQL Query Result

We are needing a Query to pull information from our Database so we can submit the information in a Text format to the Bank.

I've got most of it figured out, but the one part I can't figure out is, where the Check Number is say 5 digits long the result needs to Zero Fill the remaining with Zeros to fill it to the right for 10 Digits.

WHEN 'O' THEN '' WHEN 'V' THEN 'C' END +'' as Val
WHERE      CHECKDT BETWEEN '02/28/12' and '02/28/12' and bankid='MYBANK' and ISNUMERIC(CHECKNO)=1

The above Query gives me the Following Result


Just after the 9001 you will see a 29974. That's my Check Number, I need Zeros added to the Left of that number based off how many digits the Check number ended up being when the Query was ran.

Is there a way to do this?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:

-- Add/pad leading zeros to numeric string for a total length of 12 - QUICK SYNTAX

DECLARE @Amount varchar(32) = '78912'

SELECT STUFF(@Amount, 1, 0, REPLICATE('0', 12 - LEN(@Amount))) -- 000000078912
Kent OlsenDBACommented:
Hi Slightly easier approach might be to use the RIGHT function.

SELECT right ('000000000000' + your_value, 12) FROM xxxx

Just make sure that the string of zeroes is as long as your desired output.

Good Luck,

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rmumpower00Author Commented:
The String of Zeros would change though as the Check Numbers grew, is there a way it will read how many digits the check number was and add the appropriate Zeros?
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Kent OlsenDBACommented:
It doesn't matter.  

Either solution above will have the length of the field hard-coded into the SQL.   REPLICATE (12 - LEN..., or RIGHT ( ..., 12)

As long as you REPLICATE enough digits, or have them hard coded into the concatenated string, you're fine.  The value '12' is the critical component.

And you can mix these two solutions.  Either can use REPLICATE or a hard coded string.  The real difference is calling LEN or RIGHT.

Good Luck,
Rmumpower00Author Commented:
I'm trying to place what you guys posted into the Query and I'm getting errors. Could you put the String in the Query that I posted to make it a bit easier on me?

Thank you
Kent OlsenDBACommented:
It's always helpful to know what errors you get.  :)  Sometimes a very simple change to your SQL is the easiest path.

  RIGHT ('000000000000' + LTRIM(CAST(CHECKNO AS VARCHAR(20))), 12) +
  CONVERT(varchar(10),CHECKDT, 112)+
  WHEN 'O' THEN '' WHEN 'V' THEN 'C' END +'' as Val
  WHERE      CHECKDT BETWEEN '02/28/12' and '02/28/12'
    and bankid='MYBANK' and   ISNUMERIC(CHECKNO)=1

Try that.  :)

Kyle AbrahamsSenior .Net DeveloperCommented:
You can set the number based on a variable though:

SELECT STUFF(@Amount, 1, 0, REPLICATE('0', 12 - LEN(@Amount))) -- 000000078912


declare @total_digits
set @total_digits = 12  -- or select from an options table)

SELECT STUFF(@Amount, 1, 0, REPLICATE('0', @total_digits  - LEN(@Amount))) -- 000000078912
Rmumpower00Author Commented:
Ged325, What you wrote looks more like what I'm trying to achieve here. Could you place it in the Query I currently have so I can just copy / paste it?

@ Kent, That query worked great! IF I can't get this to automatically determine how many digits the Check Number is and automatically fill it with the appropriate Zero's then I'll use your query and just go with Hard Coding it in.
Kent OlsenDBACommented:
You don't need to know how many digits the check number is.  Whether it's 1, 4, 12 or anything else, either of those solutions will pad it with enough leading zeros to get you a 12 character string.

Rmumpower00Author Commented:
Outstanding! That worked perfectly. Now, the only one left is the REPLACE (CAST(CHECKNET AS VARCHAR(20)), '.',  '') line.

How do I put the Zeros on this to equal 10?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.