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

た入场券 2022-11-19 15:59 106阅读 0赞

最近公司有个任务是：先数字后字符的排序，并且要支持简体，繁体最后直接在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)