Sort Letters in a Phrase using T-SQL

Sort Letters in a Phrase using T-SQL


Problem definition

This article comes up from this MSDN forum post. The problem is how can we sort the letters in a phrase just using T-SQL? To clarify the question, for instance the desired result for CHICAGO must be ACCGHIO.


Because SQL is a Declarative Language in Relational System, it does not have arrays. Table is a relational variable that presents a relation, simply it is a Set that has no order. But if someone needs to do this sort in SQL Server, for example, because of a need to sort and compare in a huge table, how can we handle it?


By using T-SQL, because it has additional features even beyond relational; there is a solution to solve this problem. By the way, the first problem is how to assign array index to letters in a phrase?
One answer is to use spt_values helper table. Following sample code shows the functionality that will use later.

SET @String = 'abc';
SELECT  SUBSTRING(@String, 1 + Number, 1) [char] , number AS [Array Index]
FROM    master..spt_values
WHERE   Number < DATALENGTH(@String)
        AND type = 'P';

The following figure shows the result of the code. It shows the array index assigned per letter.

Now it’s possible to solve the main problem. Next script produces the sample data.

/*Create sample table*/
IF OBJECT_ID('tempdb..#Text', 'U') IS NOT NULL
    DROP TABLE #Test;
      ID INT IDENTITY(1, 1) ,
      Phrase VARCHAR(255)
/*Populate the table with sample data*/
        ( Phrase )
    ( 'CHICAGO' ),
    ( 'NEW YORK' ),
    ( 'HOUSTON' ),
    ( 'SAN FRANCISCO' );

Following figure shows the sample data presentation.

Next code is the final solution.

/*This is the final solution*/
WITH    base
          AS ( SELECT   L.[char] ,
                        T.ID ,
               FROM     #Test T
                        CROSS APPLY ( SELECT    SUBSTRING(T.Phrase, 1 + Number, 1) [char]
                                      FROM      master..spt_values
                                      WHERE     Number < DATALENGTH(T.Phrase)
                                                AND type = 'P'
                                    ) L
            b1.Phrase ,
            REPLACE(( SELECT    '' + [char]
                      FROM      base b2
                      WHERE     b1.Phrase = b2.Phrase
                      ORDER BY  [char]
                          XML PATH('')
                    ), '&#x20;', ' ') AS columns2
    FROM    base AS b1;

The final result shown in the following figure.


Using this solution has two limitations that come from the spt_value helper table. These limits are:

1. Data Type

The spt_value return extra records for Unicode data types. So the data type cannot be Unicode such as NVARCHAR.

2. Data Length

 The length of the data type could be up to 2048.

See Also

Leave a Comment
  • Please add 1 and 5 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
  • Naomi  N edited Revision 8. Comment: Added toc and one more link to See Also  

  • Saeid Hasani edited Revision 7. Comment: Add See Also section.

  • Naomi  N edited Revision 6. Comment: Minor edit

Page 1 of 1 (3 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Page 1 of 1 (11 items)