SQL Server – Rearrange a String Without Using Loop Syntax

sql serversql-server-2012

So if I have a table with values:

ID
------
a1b2c3
ac2b31
db13a2

I want the result to look like this

Id
------
abc123
acb231
dba132

In other words, rearrange the string so that all of the characters appear first, followed by all of the numbers. Order is unimportant.

(And what I meant by no loop is, no traditional loop. I was looking at some CTE examples, but couldn't adapt them here.)

Best Answer

Split the string to get one row for each character, order it so numbers go last and all other characters go first and then rebuild the string.

Code below uses a numbers table to split the string and the for xml path trick to rebuild it.

declare @T table
(
  ID varchar(10)
);

insert into @T values
('a1b2c3'),
('ac2b31'),
('db13a2');

select X.X.value('text()[1]', 'varchar(10)') as ID
from @T as T
  cross apply (
              select C.C as '*'
              from dbo.Number as N
                cross apply (
                            select substring(T.ID, N.N, 1)
                            ) as C(C)
              where N.N >= 1 and 
                    N.N <= len(T.ID)
              order by case when C.C in ('0','1','2','3','4','5','6','7','8','9') 
                         then 2 
                         else 1 
                       end,
                       N.N
              for xml path(''), type
              ) as X(X);

Result:

ID
----------
abc123
acb231
dba132
Related Question