How do I sort string alphabetically
13,814
Solution 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
Solution 2
Even some optimized possibilities are there. Here two function use Bubble sort to sort the 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
Solution 3
This will sort the letters alphabetically without using a function. Rewritten for optimization after @Shnugo comment
DECLARE @t table(col varchar(4000))
INSERT @t values('kdjfh'),('zug')
SELECT
Col,
(
SELECT
chr
FROM
(SELECT TOP(LEN(Col))
SUBSTRING(Col,ROW_NUMBER() OVER(ORDER BY 1/0),1)
FROM sys.messages) A(Chr)
ORDER by chr
FOR XML PATH(''), type).value('.', 'varchar(max)'
) SortedCol
FROM
@t
Result:
col SortedCol
kdjfh dfhjk
zug guz
Related videos on Youtube
Author by
Admin
Updated on February 04, 2023Comments
-
Admin about 1 year
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
My result should look like the below
NAME ...... VWXYZ ABCDE
Any idea to get this?
-
Daniel E. over 9 yearsThis article may help you, techrepublic.com/blog/the-enterprise-cloud/…
-
Shnugo over 5 yearsThe voted answers will be very bad performers (
WHILE
loop, scalar functions...). t-clausen.dk's answer is much better, but will use far to many rows in executions. You might want to have a read here... -
Sean LangeWhenver I see things like this I have to wonder if there are multiple values in a single intersection (which violates 1NF) or if this is just a classroom assignment. This kind of thing in the real world is a real challenge.
-
Tim Schmelter@MohithKm: no, you either have to write a t-sql function yourself, use a .NET method(you can also use .NET in SQL-Server) or refactor your database design. The easiest would be to sort in on client-side with .NET where you want to show it.
-
-
Shnugo over 5 yearsHi, this will use far to many rows in any execution... You might want to read this answer. Over there I provide an approach to limit the actual count of rows to the actual count of characters per string. But +1 from my side as this is much better, that the other answers ;-)
-
Shnugo over 5 yearsThis will be very slow... If interested you might want to read my comment below the question...
-
Shnugo over 5 yearsThis will be very slow... If interested you might want to read my comment below the question...
-
t-clausen.dk over 5 years@Shnugo optimized and rewritten