Order by Number & Text - 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)

Order by Number & Text

Author

Topic

Leigh79
Starting Member

28 Posts

Posted - 2009-05-05 : 05:09:13

Hi Guys

I have a field which stores House Numbers/Names as varchar.

When I order the field it displays the info as:

1
10
12
14
16
2
20
22
24...

I would prefer it to order numerically i.e.:

1
2
10
12
14
16...

Is there anyway for me to do this, the field has to be a text field as some of the information is stored as text i.e. 14 AILSA WAY, BROOK HOUSE etc.

Any help would be appreciated

Thanks
Leigh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-05 : 05:34:03

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/07/21/ordering-interger-values-stored-in-varchar-column.aspx

Madhivanan

Failing to plan is Planning to fail