Create a String With a Specific Number of Spaces in SQL Server

Question

So I'm trying to create a stored procedure in SQL Server that sends an email that is formatted to look a certain way. I want it to look like:

##date##    ##credit##
            ##debit##

Where the debit and credit columns line up exactly. The problem is that everything I try results in:

##date##    ##credit##
        ##debit##

I have tried:

declare @lineText nvarchar(max) =  
CONCAT(CONCAT(right(replicate(' ',25) + '##date##',25), 'Debit: $##debit##')
, char(13)
, CONCAT(right(replicate(' ',25) + ' ',25), 'Credit: $##credit##'));

And:

declare @lineText nvarchar(max) =  
CONCAT(CONCAT(LEFT('##date##' + replicate(' ', 25), 25), 'Debit: $##debit##')
, char(13)
, CONCAT(LEFT('' + replicate(' ', 25), 25), 'Credit: $##credit##'));

And:

declare @lineText nvarchar(max) =  
CONCAT(CONCAT(LEFT('##date##' + space(30), 30), 'Debit: $##debit##')
, char(13)
, CONCAT(LEFT('' + space(30), 30), 'Credit: $##credit##'));

And:

declare @lineText nvarchar(max) =  
CONCAT(CONCAT(CAST('##date##' as CHAR(30)), 'Debit: $##debit##')
, char(13)
, CONCAT(CAST('' as CHAR(30)), 'Credit: $##credit##'));

Nothing makes the columns line up. How do I create a string where the credit and debit columns actual align?

I want the space on the bottom line to contain the same amount of spaces as the date in the top line.

Thanks.

The second one looks correct to me when I end it with PRINT @lineText. Is it perhaps an issue with the font you are using (i.e. it's not fixed-width)? — Chris Mack
– Chris Mack, Commented Oct 8, 2019 at 15:30

Sean Lange · Accepted Answer · 2019-10-08 15:41:00Z

2

Using spaces for formatting in html is never going to work well. Probably the easiest solution is to just use an html table. I think you would want three columns for this to line up nice and straight. Something like this is pretty clean.

declare @lineText nvarchar(max) = '<table><tr><td rowspan="2" valign="top">##date##</td><td>Debit:</td><td>$##debit##</td></tr><tr><td>Credit:</td><td>$##credit##</td></tr></table>'

answered Oct 8, 2019 at 15:41

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

J.Cart Over a year ago

Yup, you're right. HTML table is the only way I could get it to work. Thanks

Collectives™ on Stack Overflow

Create a String With a Specific Number of Spaces in SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related