sorting a given array in sql server

Question

In SQL, there's no sorting function to sort a string.

I have this function, however there seems to be a problem with it.

Any help would be appreciated.

ALTER FUNCTION [dbo].[Sorting] (@str VARCHAR(5000))
returns VARCHAR(5000)
  BEGIN
      DECLARE @len    INT,
              @cnt    INT =1,
              @str1   VARCHAR(5000)='',
              @output VARCHAR(5000)=''

      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(5000)') 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

"there seems to be a problem": you need to tell us what the problem is. — Richard
– Richard, Commented Mar 18, 2019 at 7:46

KrishnakumarS · Accepted Answer · 2019-03-15 10:04:51Z

1

Do you face any issues with your function?

A modified version without using XML is given below.

-- Prior to SQL Server 2016
CREATE FUNCTION [dbo].[Sorting] (@str VARCHAR(5000))
returns VARCHAR(5000)
  BEGIN
      DECLARE @len    INT,
              @cnt    INT =1,
              @str1   VARCHAR(5000)='',
              @output VARCHAR(5000)='';

     DECLARE @chr TABLE (c VARCHAR(10));

      SELECT @len = Len(@str)
      WHILE @cnt <= @len
        BEGIN

            INSERT INTO @chr (c)
            SELECT Substring(@str, @cnt, 1)

            SET @cnt+=1
        END

        SELECT @output = @output + c 
        FROM @chr ORDER BY c;

        RETURN @output
  END
GO

If you are using SQL Server 2016 and above then you can use the SPLIT_STRING function as below

-- Since SQL Server 2016
CREATE FUNCTION [dbo].[Sorting] (@str VARCHAR(5000))
returns VARCHAR(5000)
  BEGIN
      DECLARE @len    INT,
              @cnt    INT =1,
              @str1   VARCHAR(5000)='',
              @output VARCHAR(5000)='';

     DECLARE @chr TABLE (c VARCHAR(10));

      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 = @output + value 
        FROM STRING_SPLIT(@str1, ',') ORDER BY value;

        RETURN @output
  END
GO

answered Mar 15, 2019 at 10:04

KrishnakumarS

1242 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

yassine mhadhbi Over a year ago

yeah i'm facing problems with my function it doesn't work on certain string and i don't know which one

yassine mhadhbi Over a year ago

i still have the same error Msg 537, Niveau 16, État 2, Ligne 59 Invalid length parameter passed to the LEFT or SUBSTRING function.

Zhorov · Accepted Answer · 2019-03-15 11:32:22Z

If I understand you question correctly, you may try to change your UDF like this:

Function:

CREATE FUNCTION [dbo].[Sorting] (@text VARCHAR(5000)) RETURNS VARCHAR(5000)
BEGIN
    ;WITH TallyCTE (n) AS (
        -- 10000 rows
        SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
        CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
        CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
        CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n)
    ), SymbolsCTE (s) AS (
        SELECT SUBSTRING(@text, n, 1)
        FROM TallyCTE
        WHERE n <= LEN(@text)
    )
    SELECT @text = (
        SELECT CONCAT(s, '')
        FROM SymbolsCTE
        ORDER BY s
        FOR XML PATH(''),TYPE
        ).value('.','varchar(5000)')
    RETURN @text
END

Statement:

SELECT dbo.Sorting('QWERTYUIOPASDF1234GHJKL5678ZXC90VBNM')

Output:

0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ

Collectives™ on Stack Overflow

sorting a given array in sql server

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related