如何按字母顺序对字符串进行排序 [英] How do I sort string alphabetically

问题描述

我是SQL Server以及堆栈溢出的新手.请原谅我的错误.

I am new to SQL Server as well as to Stack overflow. Please excuse for my mistakes.

是否可以按字母顺序对列中的值进行排序?这是我的桌子

Is it possible to sort a value in a column aphabetically? Here is my table

NAME
......
XZYVW
EBACD

我的结果应如下图所示

NAME
......
VWXYZ
ABCDE 

有什么想法要得到这个吗?

Any idea to get this?

推荐答案

创建User Defined Scalar Function.

CREATE FUNCTION dbo.Alphaorder (@str VARCHAR(50))
returns VARCHAR(50)
  BEGIN
      DECLARE @len    INT,
              @cnt    INT =1,
              @str1   VARCHAR(50)='',
              @output VARCHAR(50)=''

      SELECT @len = Len(@str)
      WHILE @cnt <= @len
        BEGIN
            SELECT @str1 += Substring(@str, @cnt, 1) + ','

            SET @cnt+=1
        END

      SELECT @str1 = LEFT(@str1, Len(@str1) - 1)

      SELECT @output += Sp_data
      FROM  (SELECT Split.a.value('.', 'VARCHAR(100)') Sp_data
             FROM   (SELECT Cast ('<M>' + Replace(@str1, ',', '</M><M>') + '</M>' AS XML) AS Data) AS A
                    CROSS APPLY Data.nodes ('/M') AS Split(a)) A
      ORDER  BY Sp_data

      RETURN @output
  END

结果:

SELECT dbo.Alphaorder ('XZYVW') --VWXYZ

这篇关于如何按字母顺序对字符串进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！