Column header underline length consistency

I have noticed the LEFT TSQL statement within SSMS (Results to Text) returns a different length column header underline if you use a declared integer variable rather than a hard coded integer as can be seen in the following example:

 SELECT Hi = LEFT('Stuart', 3)

 SELECT Hi = LEFT('Stuart', @x)

Does anyone know why this is? Also why does the first example have four underline characters rather than the expected three?

more ▼

asked Jan 11, 2012 at 03:44 AM in Default

avatar image

170 11 11 16

Thanks Kev for getting my question to show the right number of underline characters. How did you do that? I obviously missed something about formatting questions on this site and was getting anything but what I wanted to show.

Jan 11, 2012 at 03:53 AM StuKay

just formatted the output as 'code' : either highlight the text and click the code button, or prefix each line with 4 spaces

Jan 11, 2012 at 03:55 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

The number of underlines seems to be determined by the largest of:

  1. The column label

  2. The maximum length of the data

  3. 4

4 seems to be the minimum : try SELECT Hi = LEFT('Stuart', 1) - still 4

The reason for the second one returning 6, is that at parse time, there is no idea of what @x is, but the max it could be is 6 (length of 'stuart'), so that is used.

Nothing specific to the LEFT function at all.

more ▼

answered Jan 11, 2012 at 03:54 AM

avatar image

Kev Riley ♦♦
64.1k 48 61 81

Gah. You must have submitted your answer just as I was starting on mine. Oh well...

Jan 11, 2012 at 04:00 AM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

If you try

 DECLARE @x INT = 3 SELECT Hi = LEFT('Ethel The Aardvark', @x)

you get a line of 18 -s as a column header separator. Which implies that, for LEFT function calls where the length parameter is dynamic (ie a variable), the column header is created as the original length of the string.

If you try

 SELECT Hi = LEFT('Stuart', 1)

you still get 4 -s in the header.

Try fiddling with other select statements, and you quickly find that integer columns are built to the length of the maximum number that can be held, and other similar limits elsewhere.

Unless... the column name is longer than the output - in which case the column name length takes precedence.

Why this is? So that the data can be formatted on the fly before all results are back.

more ▼

answered Jan 11, 2012 at 03:59 AM

avatar image

ThomasRushton ♦♦
40k 20 49 52

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 11, 2012 at 03:44 AM

Seen: 1770 times

Last Updated: Jan 11, 2012 at 03:49 AM

Copyright 2016 Redgate Software. Privacy Policy