다음을 통해 공유

Sort Letters in a Phrase using T-SQL

  • 아티클

 

Problem definition

This article comes up from this creative question in MSDN forum. 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