Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2011-10-25
Medium Priority
Last Modified: 2012-05-12
Im trying to create a simple autocomple on my asp.net/vb.net 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

Accepted Solution

Eric3141 earned 2000 total points
ID: 37025737
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.

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 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