SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Concatenate Fields, But Evenly Space


Concatenate Fields, But Evenly Space

Author
Message
DarthBurrito
DarthBurrito
SSC-Enthusiastic
SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)

Group: General Forum Members
Points: 129 Visits: 365
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.
Attachments
Capture02.JPG (18 views, 32.00 KB)
Bill Talada
Bill Talada
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1139 Visits: 1992

declare @s varchar(20);
set @s = 'October 2013';
select REPLACE(@s,' ', SPACE(20 - LEN(@s)))

returns:
October 2013


Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16284 Visits: 19059
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
DarthBurrito
DarthBurrito
SSC-Enthusiastic
SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)

Group: General Forum Members
Points: 129 Visits: 365
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
Ed Wagner
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16459 Visits: 10050
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search