How do I Zero Fill a SQL Query Result
Posted on 2012-03-14
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.
SELECT '000123456789001'+LTRIM(CAST(CHECKNO AS VARCHAR(20)))+ CONVERT(varchar(10),CHECKDT, 112)+ REPLACE (CAST(CHECKNET AS VARCHAR(20)), '.', '')+CASE(CKSTATUS)
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?