Hi

I want to add spaces (like space - len(col)) to first column so that second column will be alligned when exported to email (text).

DECLARE @ColumnSpaces TABLE (
 Col_1 VARCHAR(50),
 Col_2 VARCHAR(50)
 )
INSERT INTO @ColumnSpaces VALUES ('AAA', '123')
INSERT INTO @ColumnSpaces VALUES ('AAAAAAAAAAAAAAA',

Hello Thomas,

Convert the VarChar value to Char; it has a fix width with trailing spaces:

with cte as
  (SELECT 'AAA' AS Col1, '123' AS Col2
   UNION ALL
   SELECT 'BBBBBBBBBBB', '345')
SELECT CONVERT(char(50), Col1) + Col2
FROM cte

Even when you have the same number of characters, you have no guarantee, that the e-mail reader uses a monospace font.

Use a HTML formatted e-mail body with a simple HTML table.

Otherwise use a CAST to CHAR(X). E.g.

DECLARE @ColumnSpaces TABLE
    (
      Col_1 VARCHAR(50) ,
      Col_2 VARCHAR(50)
    );

INSERT  INTO @ColumnSpaces
VALUES  ( 'AAA', '123' );
INSERT  INTO @ColumnSpaces
VALUES  ( 'AAAAAAAAAAAAAAA', '123' );

SELECT  '"' + CAST(CS.Col_1 AS CHAR(50)) + '"' ,
        '"' + CAST(CS.Col_2 AS CHAR(50)) + '"'
FROM    @ColumnSpaces CS;

• Edited by Stefan HoffmannMVP 14 hours 46 minutes ago
• Marked as answer by Royal Thomas 14 hours 26 minutes ago

Like this?

DECLARE @ColumnSpaces TABLE (Col_1 VARCHAR(50), Col_2 VARCHAR(50))
INSERT INTO @ColumnSpaces VALUES ('AAA', '123')
INSERT INTO @ColumnSpaces VALUES ('AAAAAAAAAAAAAAA', '123')

SELECT LEFT(col_1+REPLICATE(' ',(SELECT MAX(LEN(Col_1)) FROM @ColumnSpaces)),(SELECT MAX(LEN(Col_1)) FROM @ColumnSpaces)), col_2
  FROM @ColumnSpaces

SQL is a tiered architecture. That means that we do display formatting in a presentation layer. What you are trying to write is 1960's COBOL, but you are using T-SQL! 

We can give you a kludge, but would you rather be a good programmer?