How to sort a string alphabetically

Question

I am new to SQL Server as well as stack overflow. Sorry for my mistakes.

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

NAME ...... XZYVW EBACD 

My result should look like this

 NAME ...... VWXYZ ABCDE 

Any idea to get this?

Answer 1

Create a 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 

Result:

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

Answer 2

There are even some optimized features. Here two functions use Bubble sort to sort char.

 CREATE FUNCTION udf_SortString ( @string VARCHAR(1000) ) RETURNS VARCHAR(1000) AS BEGIN DECLARE @len TINYINT DECLARE @i TINYINT DECLARE @currentchar CHAR(1) DECLARE @swapped BIT DECLARE @begin BIT DECLARE @nextchar CHAR(1) SET @begin = 1 SET @len = LEN(@string) SET @i = 1 WHILE @begin = 1 OR @swapped = 1 BEGIN SET @swapped = 0 SET @i = 1 SET @begin = 0 WHILE @i <= @len BEGIN SET @currentchar = SUBSTRING(@string, @i, 1) SET @nextchar = SUBSTRING(@string, @i + 1, 1) IF @currentchar > @nextchar AND (@nextchar > '') BEGIN SET @string = dbo.udf_swap(@string, @i, @i + 1) SET @swapped = 1 END SET @i = @i + 1 END END RETURN(@string) END 

Function 2:

 CREATE FUNCTION dbo.udf_Swap ( @fullstring VARCHAR(1000), @charlocation1 TINYINT, @charlocation2 TINYINT ) RETURNS VARCHAR(1000) AS BEGIN DECLARE @returnval varchar(1000) DECLARE @begin VARCHAR(1000), @middle VARCHAR(1000), @end VARCHAR(1000) DECLARE @firstchar CHAR(1), @secondchar CHAR(1), @len INT SET @fullstring = LTRIM(RTRIM(@fullstring)) SET @len = LEN(@fullstring) IF @charlocation1 > @len OR @charlocation2 > @len SET @returnval = @fullstring ELSE BEGIN SET @firstchar = SUBSTRING(@fullstring, @charlocation1, 1) SET @secondchar = SUBSTRING(@fullstring, @charlocation2, 1) SET @begin = LEFT(@fullstring, (@charlocation1-1)) SET @middle = SUBSTRING(@fullstring, @charlocation1+1, (@ charlocation2-@charlocation1 )-1) SET @end = SUBSTRING(@fullstring, @charlocation2+1, @len) SET @returnval = @begin + @secondchar + @middle + @firstchar + @end END RETURN(@returnval) END 

Result:

 select dbo.udf_SortString('zxcvbfgrtyuijklm') --Returns bcfgijklmrtuvxyz 

- Link

- quick demo here

Answer 3

This will sort the letters alphabetically without using a function.

 DECLARE @t table(col varchar(4000)) INSERT @t values('kdjfh'),('zug') ;WITH t as ( SELECT 0 v FROM (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) x(a) ), u as ( SELECT row_number() over(order by (select 1))p FROM t CROSS JOIN t t2 CROSS JOIN t t3 CROSS JOIN t t4 ) SELECT col, ( SELECT substring(col,p,1) FROM u ORDER BY substring(col,p,1) for xml path(''), type ).value('.', 'varchar(max)') [SortedCol] FROM @t 

Result:

 col SortedCol kdjfh dfhjk zug guz