Solved

How do I Zero Fill a SQL Query Result

Posted on 2012-03-14
10
977 Views
Last Modified: 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
FROM dbo.CHECK
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

00012345678900129974201202281234569

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?
0
Comment
Question by:Rmumpower00
  • 4
  • 4
  • 2
10 Comments
 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 250 total points
ID: 37720396
http://sqlusa.com/bestpractices2005/padleadingzeros/

-- 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
0
 
LVL 45

Accepted Solution

by:
Kdo earned 250 total points
ID: 37720407
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,
Kent
0
 

Author Comment

by:Rmumpower00
ID: 37720427
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?
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 45

Expert Comment

by:Kdo
ID: 37720455
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,
Kent
0
 

Author Comment

by:Rmumpower00
ID: 37720494
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
0
 
LVL 45

Expert Comment

by:Kdo
ID: 37720525
It's always helpful to know what errors you get.  :)  Sometimes a very simple change to your SQL is the easiest path.

SELECT
  '000123456789001'+
  RIGHT ('000000000000' + LTRIM(CAST(CHECKNO AS VARCHAR(20))), 12) +
  CONVERT(varchar(10),CHECKDT, 112)+
  REPLACE (CAST(CHECKNET AS VARCHAR(20)), '.',  '')+
  CASE(CKSTATUS)
  WHEN 'O' THEN '' WHEN 'V' THEN 'C' END +'' as Val
  FROM dbo.CHECK
  WHERE      CHECKDT BETWEEN '02/28/12' and '02/28/12'
    and bankid='MYBANK' and   ISNUMERIC(CHECKNO)=1

Try that.  :)

Kent
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 37720536
You can set the number based on a variable though:

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

becomes:

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
0
 

Author Comment

by:Rmumpower00
ID: 37720556
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.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 37720583
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.


Kent
0
 

Author Comment

by:Rmumpower00
ID: 37720665
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?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question