ms sql server - manage appearance of the query...

Posted on 2011-10-25
Last Modified: 2012-05-12
Im trying to create a simple autocomple on my web form..I hear usercontrols are nicer..but im going the cheap route with this..this query will then pull into a dropdown box on my web form.  the query works, however I am wantingthe columns to line up and right now they dont. Any suggestions?

so for example there are 4 columns and they look like this:
[dddd ] [rrrr][ ] [hhhhhhhhh]

when it needs to look like this:
[dddd ] [rrrr          ][     ][hhhhhhhhh]
[hh    ] [ssssssssss ][aaa][eeeeeeeeee]

the columns should line up...

below is the query, if you run it with your own data you will see what I am saying...
SELECT '[' + LEFT(cast(name as nvarchar(max)),30) + '] [' + LEFT(cast(city as nvarchar(max)),20) + '] [' + LEFT(cast(shoeid as nvarchar(max)),20) + '] [' + LEFT(cast(shoetype as nvarchar(max)),20) + ']' 
                         FROM  shoe descrpt 
                         WHERE name like '%q%'
                         and  Type <> 'shoe' 
                         ORDER BY Name ASC

Open in new window

Question by:GlobaLevel
    1 Comment
    LVL 2

    Accepted Solution

    Using varchar I'm not sure that's possible as obviously the width of each column is variable.  If you use CHAR datatype instead then column widths would be consistent but you would have spaces at the end of the field values.  If you can live with that then try char instead of varchar.

    Also I noticed that you used varchar(max) but you only wanted to use the left most 30 characters.  varchar(max) is for when you have > 4,000 characters in nvarchar format I think.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    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

    12 Experts available now in Live!

    Get 1:1 Help Now