How to sort the words of a single cell in an SQL table?

Question

For example:

• Pillars 101 in an apartment
• Zuzu Durga International Hotel
• Wyndham Garden Fresh Meadows

Need to sort the above as,

• 101 an apartment in Pillars
• Durga Hotel International Zuzu
• Fresh Garden Meadows Wyndham

Answer 1

Try this:

DECLARE @tbl TABLE(YourString VARCHAR(100));
INSERT INTO @tbl VALUES
 ('Pillars 101 in an apartment')
,('Zuzu Durga International Hotel')
,('Wyndham Garden Fresh Meadows');

SELECT CAST('<x>' + REPLACE((SELECT YourString AS [*] FOR XML PATH('')),' ','</x><x>') + '</x>' AS XML).query
('
    for $x in /x
    order by $x
    return
    concat($x/text()[1], " ")   
').value('.','varchar(max)')
FROM @tbl; 

The code will frist transfer your text in an XML like <x>Pillars</x><x>101</x> ....
Then a FLWOR XQuery is used to return the text parts sorted.
The last call to .value() will return the sorted fragments as text again.

The result

101  Pillars  an  apartment  in 
Durga  Hotel  International  Zuzu 
Fresh  Garden  Meadows  Wyndham 

Final statement

This code is kind of an exercise. Your design is really bad and should be changed...

Answer 2

So there's nothing that you can do natively. If you want to sort the values just as a return value, i.e. not update the database itself, you can transform the results with either a stored procedure or perhaps a view.

So let's construct an answer.

Let's just assume you want to do it visually, for a single row. If you have SQL 2016 you can use STRING_SPLIT but SQL Fiddle doesn't, so I used a common UDF fnSplitString

http://sqlfiddle.com/#!6/7194d/2

SELECT value FROM fnSplitString('Pillars 101 in an apartment', ' ') WHERE RTRIM(value) <> '';

That gives me each word, split out. What about ordering it?

SELECT value FROM fnSplitString('Pillars 101 in an apartment', ' ') WHERE RTRIM(value) <> '' ORDER BY value;

And if I want to do it for each row in the DB table I have? http://sqlfiddle.com/#!6/7194d/8

SELECT split.value FROM [Data] d CROSS APPLY dbo.fnSplitString(IsNull(d.Value,''), ' ') AS split WHERE RTRIM(split.value) <> '' ORDER BY value;

That's sort of helpful, except now all my words are jumbled. Let's go back to our original query and identify each row. Each row probably has an Identity column on it. If so, you've got your grouping there. If not, you can use ROW_NUMBER, such as:

SELECT ROW_NUMBER() OVER(ORDER BY d.Value) AS [Identity] -- here, use identity instead of row_number , d.Value FROM [Data] d

If we then use this query as a subquery in our select, we get:

http://sqlfiddle.com/#!6/7194d/21

SELECT d.[Identity], split.value FROM ( SELECT ROW_NUMBER() OVER(ORDER BY d.Value) AS [Identity] -- here, use identity instead of row_number , d.Value FROM [Data] d ) d CROSS APPLY dbo.fnSplitString(IsNull(d.Value,''), ' ') AS split WHERE RTRIM(split.value) <> '' ORDER BY d.[Identity], value;

This query now sorts all rows within each identity. But now you need to reconstruct those individual words back into a single string, right? For that, you can use STUFF. In my example I use a CTE because of SQL Fiddle limitations but you could use a temp table, too.

WITH tempData AS ( SELECT d.[Identity], split.value FROM ( SELECT ROW_NUMBER() OVER(ORDER BY d.Value) AS [Identity] -- here, use identity instead of row_number , d.Value FROM [Data] d ) d CROSS APPLY dbo.fnSplitString(IsNull(d.Value,''), ' ') AS split WHERE RTRIM(split.value) <> '' ) SELECT grp.[Identity] , STUFF((SELECT N' ' + [Value] FROM tempData WHERE [Identity] = grp.[Identity] ORDER BY Value FOR XML PATH(N'')) , 1, 1, N'') FROM (SELECT DISTINCT [Identity] FROM tempData) AS grp

Here's the end result fiddle: http://sqlfiddle.com/#!6/7194d/27

As expressed in comments already, this is not a common case for SQL. It's an unnecessary burden on the server. I would recommend pulling data out of SQL and sorting it through your programming language of choice; or making sure it's sorted as you insert it into the DB. I went through the exercise because I had a few minutes to kill :)

Answer 3

Already +1 on Shnugo's solution. I actually watch for his posts.

Just another option use a parse UDF in concert with a Cross Apply.

Example

Select B.*
 From  YourTable A
 Cross Apply ( Select Sorted=Stuff((Select ' ' +RetVal From [dbo].[tvf-Str-Parse](A.SomeCol,' ') Order By RetVal For XML Path ('')),1,1,'')  )B

Returns

Sorted
101 an apartment in Pillars
Durga Hotel International Zuzu
Fresh Garden Meadows Wyndham

The UDF if Interested

CREATE FUNCTION [dbo].[tvf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From  (Select x = Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Thanks Shnugo for making this XML safe
--Select * from [dbo].[tvf-Str-Parse]('Dog,Cat,House,Car',',')