字段排序的特例(100分)

如何将以下字段<br>1B， 2B， 2B2， XB， XB1， 4B。。。<br>按如下排列<br>XB，XB1，1B，2B，2B2，4B。。。<br>困扰了我好几天，希望有人解答

具体要求？

你是不是想X开头的数据排在最前面？

是的，是想让X开头的数据排在最前面，告诉我添加字段的就不用回复了，我希望可以用Sql2000的内部机制来解决，比如说用户定义函数、多次select然后连接为一张表。这两天我一直在试，可惜一直没成功，哪位能提供代码。不胜感激。

假定表名 TableName, 字段名 Field1:<br>select * from TableName<br>order by (case when substring(Field1,1,1)='X' then 0 else 1 end),Field1

感谢kaida，这个问题解决了，我还想多问一句，我这样为什么不行：<br>select * from TableName where Field1='X%' order by Field1<br>union<br>select * from TableName where Field1!='X%' order by Field1<br>上述总是报错，不能执行。<br>我又在sql2000内定义了如下的函数：<br>CREATE function GetXto0<br>(@Version char(10))<br>returns char(10)<br>as<br>begin<br>&nbsp; &nbsp; declare @result char(10)<br>&nbsp; &nbsp; if charindex('X',@Version)=NULL<br>&nbsp; &nbsp; &nbsp; &nbsp; begin<br>&nbsp; &nbsp; &nbsp; &nbsp; set @result=@Version<br> goto MyLoop<br>&nbsp; &nbsp; &nbsp; &nbsp; end<br>&nbsp; &nbsp; else<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;begin<br>&nbsp; &nbsp; &nbsp; &nbsp; set @result=stuff(@Version,charindex('X',@Version),1,'0')<br>&nbsp; &nbsp; &nbsp; &nbsp; end<br>&nbsp; &nbsp;MyLoop: return @result<br>end<br>倒是没报错，可是运行后发现这个函数不起作用。

1.<br>select * from TableName where Field1='X%' order by Field1<br>union<br>select * from TableName where Field1!='X%' order by Field1<br>错误： union 子句不允许有 order by<br><br>2.正确：<br>CREATE function GetXto0<br>(@Version char(10))<br>returns char(10)<br>as<br>begin<br>&nbsp; &nbsp; declare @result char(10)<br>&nbsp; &nbsp; if charindex('X',@Version)=0<br>&nbsp; &nbsp; &nbsp; &nbsp; set @result=@Version<br>&nbsp; &nbsp; else <br>&nbsp; &nbsp; &nbsp; &nbsp; set @result=stuff(@Version,charindex('X',@Version),1,'0')<br>&nbsp; &nbsp; return <br>&nbsp; &nbsp; &nbsp; &nbsp; @result<br>end<br>错误：if charindex('X',@Version)=NULL -- 应该是<br>&nbsp; &nbsp; &nbsp; if charindex('X',@Version)=0

感谢kaida的热心帮助。大富翁里果然不乏专业知识丰富的热心人。[]