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.


Introduction


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?


Solution


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.

DECLARE @String VARCHAR(MAX)
   
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;
     
CREATE TABLE #Test
    (
      ID INT IDENTITY(1, 1) ,
      Phrase VARCHAR(255)
    );
     
/*Populate the table with sample data*/
INSERT  #Test
        ( Phrase )
VALUES 
    ( '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 ,
                        T.Phrase
               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
             )
    SELECT  DISTINCT
            b1.Phrase ,
            REPLACE(( SELECT    '' + [char]
                      FROM      base b2
                      WHERE     b1.Phrase = b2.Phrase
                      ORDER BY  [char]
                        FOR
                          XML PATH('')
                    ), '&#x20;', ' ') AS columns2
    FROM    base AS b1;


The final result shown in the following figure.




Limitations


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 3 and 7 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • 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.
Comments
  • Naomi  N edited Revision 6. Comment: Minor edit

  • This article may benefit from the See Also section

  • Thanks,

    You are right. I will add it soon.

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

  • Naomi  N edited Revision 8. Comment: Added toc and one more link to See Also  

  • Thanks Naomi!!!

    You added a table of content! Now it looks great.

  • Please add your other T-SQL articles to this parent article social.technet.microsoft.com/.../17785.sql-server-query-language-transact-sql.aspx and also encourage others to do so or add other T-SQL articles you know which are not yet in that page. I want that page to be a catalog of all T-SQL articles. We may also create a similar article for SSIS or SSRS and make sure to add it to see also section in related articles.

  • Good idea. I also will add my next articles.

  • Thanks Naomi for adding  "T-SQL Guru Gold Medal Winner" tag.

    But it's not shown in this tag page:

    social.technet.microsoft.com/.../default.aspx

  • Congrats on winning the gold medal: blogs.technet.com/.../technet-guru-awards-august-2013.aspx

  • Thanks

Page 1 of 1 (11 items)