x

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)

Hi
----
Stu

DECLARE @x INT = 3
SELECT Hi = LEFT('Stuart', @x)

Hi
------
Stu
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 '12 at 03:44 AM in Default

StuKay gravatar image

StuKay
170 9 9 11

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 '12 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 '12 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 '12 at 03:54 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

Gah. You must have submitted your answer just as I was starting on mine. Oh well...
Jan 11 '12 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 '12 at 03:59 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.3k 14 20 44

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x265
x5

asked: Jan 11 '12 at 03:44 AM

Seen: 1068 times

Last Updated: Jan 11 '12 at 03:49 AM