先数字后字符的排序 PATINDEX 并且要支持简体 繁体 最好直接在mssql里实现

最近公司有个任务是：先数字后字符的排序，并且要支持简体，繁体最后直接在mssql里实现.

研究了将近一天半，终于成功！

 

通过以下三步可以实现

第一步：将database的排序规则修改成 COLLATE Chinese_PRC_CI_AI

 

ALTER database SSC_20130605  COLLATE Chinese_PRC_CI_AI

 第二步：写好排序Function

  

CREATE FUNCTION [dbo].[fnMixSort]
(
    @ColValue NVARCHAR(255)
)
RETURNS NVARCHAR(1000)
AS

BEGIN
    DECLARE @p1 NVARCHAR(255),
     @p2 NVARCHAR(255),
     @p3 NVARCHAR(255),
     @p4 NVARCHAR(255),
  @p0 NVARCHAR(255),
     @Index TINYINT


--吖-座
--一-龜

    IF @ColValue LIKE '[吖-座]%'
     SELECT @Index = PATINDEX(N'%[0-9]%', @ColValue),
      @p0 = LEFT(CASE WHEN @Index = 0 THEN @ColValue ELSE LEFT(@ColValue, @Index - 1) END + REPLICATE(' ', 255), 255),
      @ColValue = CASE WHEN @Index = 0 THEN '' ELSE SUBSTRING(@ColValue, @Index, 255) END
    ELSE
     SELECT @p0 = REPLICATE(' ', 255)

    IF @ColValue LIKE '[a-z]%'
     SELECT @Index = PATINDEX('%[0-9]%', @ColValue),
      @p1 = LEFT(CASE WHEN @Index = 0 THEN @ColValue ELSE LEFT(@ColValue, @Index - 1) END + REPLICATE(' ', 255), 255),
      @ColValue = CASE WHEN @Index = 0 THEN '' ELSE SUBSTRING(@ColValue, @Index, 255) END
    ELSE
     SELECT @p1 = REPLICATE(' ', 255)


    SELECT @Index = PATINDEX('%[^0-9]%', @ColValue)

    IF @Index = 0
     SELECT @p2 = RIGHT(REPLICATE(' ', 255) + @ColValue, 255),
      @ColValue = ''
    ELSE
     SELECT @p2 = RIGHT(REPLICATE(' ', 255) + LEFT(@ColValue, @Index - 1), 255),
      @ColValue = SUBSTRING(@ColValue, @Index, 255)

    SELECT @Index = PATINDEX(N'%[0-9,a-z,吖-座]%', @ColValue)

    IF @Index = 0
     SELECT @p3 = REPLICATE(' ', 255)
    ELSE
     SELECT @p3 = LEFT(REPLICATE(' ', 255) + LEFT(@ColValue, @Index - 1), 255),
      @ColValue = SUBSTRING(@ColValue, @Index, 255)

   

    IF PATINDEX('%[^0-9]%', @ColValue) = 0
     SELECT @p4 = RIGHT(REPLICATE(' ', 255) + @ColValue, 255)
    ELSE
     SELECT @p4 = LEFT(@ColValue + REPLICATE(' ', 255), 255)

 

    RETURN @p0+@p1 + @p2+ @p3 + @p4

END 

 第三步:应用写好的Function排序.

 

 

SELECT ArchiveName FROM TB_Archive ORDER BY dbo.fnMixSort(ArchiveName)