Padding Columns?

Posted on 2005-04-27
Last Modified: 2010-03-19
Hello I need a function to pad columns (int and varchar).

select name +' '+ address+' '+city+' '+id from users;

should return:

Mike            88 evergreen terrace      halifax
Jason Jones      16 way ave            Halifax

In other words all columns should be x numbers of characters wide after being padded.

Any suggestions?
Question by:91mustang
    LVL 4

    Author Comment

    oops formatting did not hold up...

    The columns that make up the one field that gets returned should line up.
    LVL 4

    Author Comment

    mike   street   city   1
    jon     street   city   1
    LVL 28

    Accepted Solution

    Try this:

    SELECT Name + SPACE(80 - LEN(Name)) + Address + SPACE(100 - LEN(Address)) + ID
    FROM Users

    This assumes that you want Name to be 80 characters long and address to be 100 characters long.
    LVL 12

    Expert Comment


    Rafrancisco's approach is right. Here is all the columns that you have listed assuming that you want them to be 50 chars width:

    select name+space(50-len(name))+address+space(50-len(address))+city+space(50-len(city))+id
    from users


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now