[SQL Server]SQL字符串字段内的字符排序

楼主^#

更多发布于：2012-12-13 15:07


	SQL字符串字段内的字符排序排序要求： www.atcpu.com 1.首先安装 #号前面的字符大小排序 2.再按照 / 左右消除的结果排序例如： 1/4 < 5/16 排序前 1/4-20 10#-24 2#-56 4#-40 5/16-18 6#-32 8#-32 排序后结果 2#-56 4#-40 6#-32 8#-32 10#-24 1/4-20 5/16-18 SQL: --> 测试数据：[TB] if object_id('[TB]') is not null drop table [TB] GO create table [TB]([col] varchar(7)) insert [TB] select '1/4-20' union all select '10#-24' union all select '2#-56' union all select '4#-40' union all select '5/16-18' union all select '6#-32' union all select '8#-32' SELECT * FROM TB ORDER BY CASE WHEN CAST(PARSENAME(REPLACE(col,'#','.'),2) AS int) IS NULL THEN 1 ELSE 0 END, CAST(PARSENAME(REPLACE(col,'#','.'),2) AS int), PARSENAME(REPLACE(col,'/','.'),2) /* col ------- 2#-56 4#-40 6#-32 8#-32 10#-24 1/4-20 5/16-18 (7 行受影响) */ drop table [TB]