-3

I am dynamically creating a column via a SELECT statement. The content of this column is a string which is then used as content for an e-mail.

The text looks like this:

text 1 | text 2 | text 3 | ...
text 123 | text 223 | text 323 | ...

as you see, the two lines are not aligned. The expected output should be like this:

text 1   | text 2   | text 3   | ...
text 123 | text 223 | text 323 | ...

The obvious solution is to pad each field in order for each field to have the same size. for this i used the following code:

LEFT(field1 + REPLICATE(' ', 20), 20)

My issue is, that in Outlook the text is still not shown properly aligned even though, if I copy the text into Notepad, it is properly aligned. The reason is, that Outlook uses Arial for displaying text-mails and in Arial blank-spaces do not have the same size as the other characters. Hence the padding does not yield the expected result.

My question now is: Is there some other way to use this padding approach in order to get same size displayed fields in Outlook Mails? Probably by padding with tabs? But I think my approach above then does not work anymore...

beta
  • 5,324
  • 15
  • 57
  • 99
  • 1
    Dude SQL is not meant for this. Simply copy paste the result to excel and add border then copy that to outlook – Pரதீப் Oct 14 '16 at 15:53
  • 2
    Prepare HTML content for your email. – techspider Oct 14 '16 at 15:57
  • 100% agree with @Prdp but you may want to look at `sp_send_dbmail` which you can send results as plain-text, html, or as an attachment... including excel. – S3S Oct 14 '16 at 16:01
  • In [this answer](http://stackoverflow.com/a/39487565/5089204) I found a fully generic approach to transfer (almost) any `SELECT` into a html table – Shnugo Oct 14 '16 at 16:05

2 Answers2

1

Regarding the TAB, it dawned on me that you were talking about trailing tabs

Declare @Table table (SomeField varchar(50))
Insert into @Table values
('Some Text'),
('Some Longer Text with more')

Select String = SomeField+replicate(char(9),(30-Len(SomeField))/4)+'|'
From @Table

Returns

Some Text                   |
Some Longer Text with more  |
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • hm almost there, but not quite. see this screenshot: http://i.imgur.com/un6FnMN.png for the first string (first line, it has 21 characters) 5 tabs are created. for the second string (20 characters) four tabs are created. the math is correct, but it yields one tab too much for the first string. note, that I changed in your code `30` to `40` – beta Oct 17 '16 at 07:17
  • okay, for some reason, strings of size 4,8,12,16, and so on had one additional tab which shouldnt have been there. I now check for these cases and remove one of the tabs. – beta Oct 17 '16 at 07:55
0

If you don't want (or can't) create the HTML as @techspider and @Shnugo mentioned, you could simply cast your string as a char(20)

cast(field1 as char(20))

For Example

Select '| '+cast('text1' as char(20))+' | '+cast('text1' as char(20))+'|'

Returns

| text1                | text1               |
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • do you know a solution where I can pad with `tabs`? the issue is that I need a different amount of tabs, because the fields can be of different size. – beta Oct 14 '16 at 16:12
  • @beta Not clear what you are looking for. A visual would help. Replicate(char(9),5) (5 tabs) or Replicate('\t',5) (encoded) – John Cappelletti Oct 14 '16 at 16:19
  • because strings are of different size, replicating always 5 tabs does not help. for some shorter strings, i maybe need 6 tabs. for longer strings, i need only 3 tabs in order to get same size fields. you know what i mean? – beta Oct 14 '16 at 16:21
  • @beta the 5 was just an example. This can be an expression. Tabs generate a consistent lengths depending on target (some 4, others 10), in other words, 3 tabs + a 4 character string will not be the same length as 3 tabs plus a 5 character string. cast as char or padding is the only way to enforce lengths. – John Cappelletti Oct 14 '16 at 16:33
  • I think the point is character width... If OP could enforce a *fixed-width* font simple padding would be perfect. With modern fonts you must rely on presentation logic of the client. Tabs might be supported - or not... HTML table might be supported - or not... – Shnugo Oct 14 '16 at 17:00
  • @Shnugo As you mentioned, HTML Formatted email would be the way to go. That would eliminate all but one concern (client doesn't accept HTML email, which I suspect would be very rare). – John Cappelletti Oct 14 '16 at 17:15
  • @Shnugo Just dawned on me that OP was talking about trailing blanks .. added another answer – John Cappelletti Oct 14 '16 at 17:25
  • yes, i was talking about trailing blanks. and @Shnugo is correct: Outlook-Client doesnt use fixed-width font for displaying raw-text-mails. hence, padding with blanks does not work. of course, HTML would be the way to go, but our code does currently not support it and we would need a quick solution. but of course, we already requested to get HTML mails.. but still would like to get it to work without HTML. I can try your other solution on monday. – beta Oct 15 '16 at 07:04