Concatenate into fixed length

Question

Post reply

Concatenate into fixed length

Bosco2

Ten Centuries

Points: 1240
More actions
June 4, 2010 at 11:29 pm

#236082

OK it is late and I am having trouble keeping my eyes open so please forgive this easy question that I am drawing a complete blank on:
I am perform "Select UserNUM + FirstName + LastName from TableA"
It retuns:
12345JohnSmith
I can add spaces "Select UserNUM + ' ' + FirstName + ' ' + LastName from TableA"
Which return:
12345 John Smith
12 Dave Brown
What do I need to do ino order to set them up as a fixed length so reagrdless of the data they will all line-up?
Example:
12345 John Smith
12 Dave Brown
Thanks.....

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

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

Bosco2 Ten Centuries Points: 1240 More actions · Answer 1

So no one knows a simple way to do this? All I want is the output to look be lined up:

OUTPUT:

12345 Allen Brown

33 David Wilson

All I get now is:

12345 Allen Brown

33 David Wilson

HELP!!!

Bosco2 Ten Centuries Points: 1240 More actions · Answer 2

Sorry I realize the forum is removing my formatting......All I want is the 3 fields to return in a fixed length so they line up....... each field (regardless of the length of data in the field) to return a length of 20. So if it has 5 charaters it will put 15 spaces......if if is 10 characters it will return 10 spaces.......everythign will line up.

I know there has to be some simple command:

select CAST (UserID as CHAR(50)) +

CAST (FirstName as CHAR(50)) +

CAST (LastName as CHAR(50)) +

CAST (Dept as CHAR(50))

from TABLEA

????????

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

Is this what you are looking for:

CREATE TABLE #TableA(UserNum INT, FirstName VARCHAR(20), LastName VARCHAR(30))

INSERT INTO #TableA

SELECT 1,'Michael','LongerName' UNION ALL

SELECT 12345, 'John', 'Smith' UNION ALL

SELECT 12, 'Dave', 'Brown'

SELECT CAST(UserNum AS CHAR(10)), CAST(FirstName AS CHAR(20)), LastName from #TableA

Returned:

12345 John Smith

12 Dave Brown

1 Michael LongerName

I realize the forum is removing my formatting

Next time post your desired output between the code='sql' tags as this will help preserve spacing.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Vladan SSC-Insane Points: 22072 More actions · Answer 4

CAST as CHAR should help, maybe you're viewing the result in GridView? There it looks like the results don't have the same length, but if you copy the result into Notepad (or ManagementStudio), you'll see that they do... same if you set Results to Text.

jbrewer Default port Points: 1453 More actions · Answer 5

Did you get a solution for your problem? I have a similar issue.

Thanks