Concatenate Fields, But Evenly Space

Question

Post reply

Concatenate Fields, But Evenly Space

Tek

SSCommitted

Points: 1697
More actions
October 23, 2013 at 10:38 am

#299497

I know how to concatenate two fields, but having issues spacing the two fields evening.
If you look at the attachment, you see that Current Results are displaying the year all jagged. I've been playing around with SPACE, but not getting it right.
Below the current results, you'll see what I'm trying to achieve. For the sake of trying to explain what I'm looking for, I just inputted the months and years manually in excel. But the point is, trying to get both fields into one, but lined up evenly like in the attachment.
Thanks for the help.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Bill Talada SSChampion Points: 11954 More actions · Answer 1

declare @s-2 varchar(20);

set @s-2 = 'October 2013';

select REPLACE(@s,' ', SPACE(20 - LEN(@s)))

returns:

October 2013

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

Where are you trying to see the names aligned? Remember that it will depend on your font type.

A similar approach to Bill's, would be:

SELECT LEFT( MonthName + SPACE(20), 20) + CAST( year AS CHAR(4))

FROM DWESTRELLAS..dimperiodos

As I have MS Sans Serif for grid results, it doesn't show aligned, but if I copy and paste on the text editor which has Courier New it shows aligned.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Tek SSCommitted Points: 1697 More actions · Answer 3

I'm using this for a parameter drop down list for a report I'm creating. Instead of having the user select a Month and then a Year in two parameters, I wanted to have the month and year in one field, but in a neat list, not all jagged like I have it now.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 4

The query Luis or Bill posted will work, but as Luis also pointed out, it will depends on the font face. If you're putting this into a standard dropdown, you're going to need to select a fixed-width font if you want them to line up neatly. If you select a true type font, you're not going to get them to line up consistently. Each character needs to occupy the same horizontal space.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions