[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SQL field padding.

Posted on 2007-07-23
Medium Priority
Last Modified: 2012-08-14
I am writing a Transact sql query that will produce data for a fixed length flat file. Is there anyway to pad the data with the appropriate number of blank spaces in the query? I can handle this in code but would prefer to in the query if it is possible.

For example: There are 166 fields and the total character length per row must be 1600 not more or less. Some fields may have no data but must contain the right amount of padding or the field may have data such as 10 chars worth but the entire field length must be 20.
Question by:jfsedlar3rd
LVL 15

Accepted Solution

derekkromm earned 1000 total points
ID: 19548675
If you need specific field lengths, you should use char instead of varchar for your column datatypes. That will automatically pad the data as needed.
LVL 10

Assisted Solution

aesmike earned 1000 total points
ID: 19548824
in your Query you will need a bunch of these:
Cast(Isnull(<yourfield>,'') as Char(x))
where <yourfield> is your field name, x is the lengh you want the field to be.
It will pad the field with spaces for you.  You need the Isnull in case you have null fields and don't want a null value

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

872 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