Rearrange a string without using loop syntax

Question

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

Answer 1

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

Answer 2

On SQL Server vNext you can take advantage of the TRANSLATE function to solve this. That version isn't generally available to the public yet but it might be useful one day or to inspire an answer from someone else.

SELECT 
REPLACE(TRANSLATE(@ID, '0123456789', SPACE(10)), ' ', '') 
+ REPLACE(TRANSLATE(@ID, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', SPACE(52)), ' ', '');

db fiddle link for your three examples in the question.

On SQL Server 2012 you could solve your problem by applying the REPLACE function 62 times but there may be a more elegant solution:

DECLARE @T table
(
    ID varchar(10) NOT NULL
);

INSERT @T
    (ID)
VALUES
    ('a1b2c3'),
    ('ac2b31'),
    ('db13a2');

SELECT
    -- Remove numerics
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        T.ID, 
        '0', ''), '1', ''), '2', ''), '3', ''), '4', ''),
        '5', ''), '6', ''), '7', ''), '8', ''), '9', '')
    +
    -- Remove alphas
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        T.ID,
        'a', ''), 'b', ''), 'c', ''), 'd', ''), 'e', ''), 'f', ''), 'g', ''),
        'h', ''), 'i', ''), 'j', ''), 'k', ''), 'l', ''), 'm', ''), 'n', ''),
        'o', ''), 'p', ''), 'q', ''), 'r', ''), 's', ''), 't', ''), 'u', ''),
        'v', ''), 'w', ''), 'x', ''), 'y', ''), 'z', ''),
        'A', ''), 'B', ''), 'C', ''), 'D', ''), 'E', ''), 'F', ''), 'G', ''),
        'H', ''), 'I', ''), 'J', ''), 'K', ''), 'L', ''), 'M', ''), 'N', ''),
        'O', ''), 'P', ''), 'Q', ''), 'R', ''), 'S', ''), 'T', ''), 'U', ''),
        'V', ''), 'W', ''), 'X', ''), 'Y', ''), 'Z', '')
FROM @T AS T;