order by value of text field

[shahina]

Hi all..

i am using asp.net and sql server 2000. I want to sort some records on the basis of house no which is a text field. i mean it have both numbers and text eg..1, 1A, 2, 2A like that..if i sort by taking house no its coming like 1,10 like that..how can i can get the report as i needed like 1, 1A, 2..Please help me..thanx in advance..

 

[Savil]

You could set up an insert trigger that separates the number and the letter into two additional columns and then order by the number and the letter