Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Concatenate Fields, But Evenly Space Expand / Collapse
Author
Message
Posted Wednesday, October 23, 2013 10:38 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 9, 2014 8:38 AM
Points: 83, Visits: 313
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.



  Post Attachments 
Capture02.JPG (12 views, 32.83 KB)
Post #1507741
Posted Wednesday, October 23, 2013 10:54 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:32 AM
Points: 152, Visits: 961
declare @s varchar(20);
set @s = 'October 2013';
select REPLACE(@s,' ', SPACE(20 - LEN(@s)))

returns:
October 2013

Post #1507753
Posted Wednesday, October 23, 2013 11:03 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:59 PM
Points: 3,919, Visits: 8,898
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1507759
Posted Wednesday, October 23, 2013 11:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 9, 2014 8:38 AM
Points: 83, Visits: 313
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.
Post #1507766
Posted Wednesday, October 23, 2013 11:45 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:00 PM
Points: 4,477, Visits: 3,929
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
Post #1507775
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse