Format an expression - SQL Server Forums

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)

Format an expression

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-08-15 : 06:33:30

I have the following sp which populates a list box on a web page (asp.net) The data appears like this

0BM1 (I) (8)
OGM2 (E) (12)
NW1B (I) (6)
.
.
.

However,due to the differing widths of I and E etc the columns aren't exactly underneath each other. Is there a way I can make them tidier

CREATE Procedure [spRB_GetRoomsIE]

@strLocation nvarchar(100),
@strPaynumber int

as
Select * ,COALESCE( RM_RoomRef + ' (' +left(RM_IntExt,1) + ')'+' ('+str(RM_MaxNo)+')' ,RM_Roomref) as LBDesc
from vweRoomsUserCanSee
WHERE RM_Location = @strLocation and
RM_NotInuse = 0 or
UR_Location =@strLocation and
UR_Paynumber = @strPaynumber

ORDER BY RM_RoomRef asc
GO

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-15 : 06:45:43

Its always good to format the data in the front end , and display them
accordingly.

Can you post same sample data how you are getting the final result set.

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-15 : 06:50:34

Yes, make sure the combo box is using COURIER NEW or any other fixed-width font.
Also use following code

SELECT		*,
		LEFT(RM_RoomRef + SPACE(12), 12) + ' (' + RIGHT('000' + CONVERT(VARCHAR, ISNULL(RM_IntExt, 0)), 3) + ') (' + RIGHT('000' + CONVERT(VARCHAR, ISNULL(RM_MaxNo, 0)), 3) AS LBDesc
FROM		vweRoomsUserCanSee
WHERE		RM_Location = @strLocation and RM_NotInuse = 0
		or UR_Location =@strLocation and UR_Paynumber = @strPaynumber
ORDER BY	RM_RoomRef

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-15 : 09:27:27

Isnt it easy to format this in ASP.NET?

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-08-15 : 10:01:09

No, because a listbox has only one column so I had to concatenate the values I wanted to display.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-15 : 10:12:04

No, you can do that with VBScript inside ASP page too, unless you are using the built-in datareader.

Peter Larsson
Helsingborg, Sweden

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-08-15 : 11:18:38

I am using asp.net not vb

If you can do it in that please can you tell me how

TIA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources