Select statement output problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select statement output problem

Author

Topic

Toshkin
Starting Member

4 Posts

Posted - 2004-09-27 : 02:48:07

Below is my select statement:

SELECT UserCode, UserID, Password, User_Title + '  ' + User_Givenname + ' ' + User_Surname + '(' + User_Suburb + ')' AS LogonAS FROM tblUsers

When executed my output displays the following:

But I need it to display like below.

I need to know how to eliminate whitespaces in tblUsers at design time.

F@#$ this it is doing my head in Im going to seek help at SQLTEAM.COM forum
Cheers Toshkin!!!

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-27 : 03:05:49

rtrim(string)

Kristen
Test

22859 Posts

Posted - 2004-09-27 : 04:09:10

You might need COALESCE too - in case one of them is NULL

COALESCE(User_Title + ' '), '') + COALECE(User_Givenname + ' ', '') + COALESCE(User_Surname, '') + COALESE ('(' + User_Suburb + ')', '')

Did you mean to have two spaces after User_Title?? No space after User-Surname?

Just being picky ...

Kristen

Toshkin
Starting Member

4 Posts

Posted - 2004-09-27 : 08:40:51

When I populate the table with entries in SQL E.Mang. it for some unknown uncanny reason maxs out each field to its set max size; ie: User_Title (varchar, 15)->Mr.____________ <--annoying whites space.Hence when I merge the columns into one column i get this result "Mr.____________ John_________________________ Doe_________________________ (BONDI_________________________)"
But I would like it to be displayed as follows: "Mr. John Doe (BONDI)" I have successfully been able to use the RTRIM but would like to not even have to use this cumbersome method is there a way in Enterprise manager to turn off whitespaces or rtrim all entries in the table once and for all.

Cheers Toshkin!!!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-27 : 08:57:25

are you sure that your fields aren't just char? just checking...

Go with the flow & have fun! Else fight the flow

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-28 : 00:29:57

use nvarchar instead of char

--edit:
how are you saving the information? from where?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources