Sort Letters in a Phrase using T-SQL

Problem definition
Introduction
Solution
Limitations
- 1. Data Type
- 2. Data Length
See Also

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

),

' ', ' ') 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.