Defining multiple fields in a select string with hard set length values (SQL 2000)

Question

Currently I have been tasked to modify a super old stored procedure that represents an email output in our SQL 2000 instance. With correcting a few syntax issues I wanted the original string format to be more presentable in the email. Because the fields "machine" and "Part #" have a variance of the fields data length thus the fields shift and the columns do not line up entirely. What I have been attempting is to set each field present in my SQL code to hard set values of say 15 characters that way the format of each field presented does not change/shift and looks straight to the end user. Now I have tried a few suggestions online and could not find a method to set each field. Any ideas?

 INSERT INTO ##prod_performance_txt_out2
               SELECT LEFT(machine + '             ', 9) 
          + LEFT(part_no + '                              ', 30) 
          + LEFT(CONVERT(varchar(7), ordered) + '       ', 7) 
          + LEFT(CONVERT(varchar(7), picked)  + '       ', 7) 
          + LEFT(CONVERT(varchar(7), allocated)  + '       ', 7)
          + LEFT(CONVERT(varchar(7), in_stock)  + '       ', 7) 
          + LEFT(CONVERT(varchar(7), sh_bin_qty)  + '       ', 7)  
          + LEFT(CONVERT(varchar(7), short)  + '       ', 7) 
          + min_bin


               FROM #tempsummary2 WHERE resource = @resource AND short > 0

Answer 1

First - note that performing detailed formatting in SQL is often frowned upon, and "should be left to the calling application". Such recommendations, of course, ignore situations like this where there is no calling application.

I don't see an issue with the fundamental methodology seen in the query you provided. In a situation like this, it's wise to use application-specific knowledge. For variable-length values that you're willing to truncate, you need to figure out what length covers most of the data, and provides enough of that column to make out what it is. For values that must be provided as is (your numeric data), you need a length will cover the maximum possible value for the field. And, that may be different for different columns: a quantity column in a table to hold data on cars at a dealership can probably be smaller than a "total value" column in the same table.

If the hard-coded values you're using need to be used on a number of lines, you can put them into variables.

I would recommend making sure that you have "gutters" between your columns - 1-3 spaces between each column helps keep them distinct and easily readable. I'm pretty sure the "LD-MOLDINLD-" lines in your sample output are run together due to the lack of a gutter.

Finally, you might want to flip the padding on the numeric columns; if you change:

LEFT(CONVERT(varchar(7), ordered) + '       ', 7)

to:

RIGHT('       ' + CONVERT(varchar(7), ordered), 7)

then the numbers will be right-aligned, which is far more typical for numbers.

---

All that said, none of this works well if your carefully spaces output isn't displayed using a mono-space font.

Looking at "MACHINE", "SHIPPING", and "MOLDINLD", I suspect the output you've provided a screen shot of is not using a mono-spaced font. Note that "MACHINE" (7 letters) and "SHIPPING" (8 letters) seem like they line up on their right side (or are off by 1 pixel).

Not knowing your email program, I can't be sure of the best way to convince it to display this data that way. For HTML output, try enclosing the entire output block in <pre><code> tags (with appropriate closing </code></pre> tags at the end. As, in fact, is done on this very page at this time (right-click, select "View source", and scroll a ways to see).

You can confirm this is correct by copying the output you took a screenshot of, and pasting it into a text editor set to use a mono-space font.