Sorting results basing on a string variable content

Question

I'm using SQL Server 2008 R2.

I would like to sort results by a specific order which I hold as a string

(the string is created programmatically).

Consider this table:

Col1    Col2    Col3
1       Jon     a
2       Joan    b
3       John    a
4       Jonnie  b
5       Jonny   a

I have an nvarchar variable declared as @myOrderString, which contains the order of rows I would like to select.

Let's say that @myOrderString = '213' (edited)

So, I would like to do something like that:

SELECT 
    ROW_NUMBER() OVER (ORDER BY @mySortString) AS Row, 
    ( Col2 + '(' + Col1 + ')' ) AS Outcome
FROM 
    myTable 
WHERE 
    Col3 = 'a' 
ORDER BY 
    @mySortString

In order to get the outcome: (edited)

Row Outcome
1   John (3)
2   Jon (1)
3   Jonny (5)

How do I even start solving this?

P.S.

If the values in @myOrderString should be separated, I can make it @myOrderString = '2,1,3' (edited)

---

The reason for the editing the question:

1. There was an error in the string content. [marked as (edited)]
2. There was an error in the results. Thanks to Andriy M. [marked as (edited)]
3. I've added cosmetic changes and corrected some grammar and typos.

---

A clarification: (part of this clarification is based on the comment by Aaron Bertrand)

1. The number of values in the string is always matching the number of rows in the result.
2. The values in the string are always the numbers 1 to the row count.
3. The first result's row is marked as 1; the 2nd is marked as 2 and the third as 3. Now, the string is meant to reorder the result. when the string is 2,1,3 it means that the select result is reordered: the 2nd row will now result as the first, the first result will appear as second, and the 3rd row will remain 3rd
4. The max row count is 15 so the string can be of the form 123456789ABCDEF, or in case the solution with this reduced form is not simple, the string can be of the form 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15

---

Thanks to many here and out there, I've reached the following solution:

(without the need for functions or loops)

-- creating the original table and filling it
DECLARE @t TABLE(Col1 INT, Col2 VARCHAR(22), Col3 CHAR(1));

INSERT @t VALUES
(1,'Jon',   'a'),
(2,'Joan',  'b'),
(3,'John',  'a'),
(4,'Jonnie','b'),
(5,'Jonny', 'a');

-- this is the required order of the results
DECLARE @myOrderString VARCHAR(32) = '213';

-- this is my current solution
SELECT 
    ROW_NUMBER()OVER (ORDER BY CHARINDEX(CAST(rr AS NVARCHAR(MAX)), @myOrderString)) As [Row],
    Outcome 
FROM 
    (
        SELECT 
            ROW_NUMBER()OVER (ORDER BY Col1) AS rr, 
            Col2 + ' (' + CONVERT(NVARCHAR(22),Col1)+ ')' AS Outcome 
        FROM @t 
        WHERE  Col3 = 'a'
    ) as r
ORDER BY 
    [Row]

but,it works for up to 9 rows of a result, while I have up to 15 rows represented of the form 123456789ABCDEF.

I tried to employ Dec to Hex convertion on result's row numbers by using CONVERT(CHAR(1),CONVERT(VARBINARY(1),@Dec)), but with no luck.

Is there a simple correction available for this to work?

---

A long example (longer than 9 rows)

In order to test an example longer than 9 rows, I use this:

-- creating the original table and filling it
DECLARE @t TABLE(Col1 INT, Col2 VARCHAR(22), Col3 CHAR(1));

INSERT @t VALUES
(1,'Jon',   'a'),
(2,'Joan',  'b'),
(3,'John',  'a'),
(4,'Jonnie','b'),
(5,'Jonny', 'a'),
(6,'Don',   'a'),
(7,'Doan',  'b'),
(8,'Dohn',  'a'),
(9,'Donnie','b'),
(10,'Donny', 'a'),
(11,'Gon',   'a'),
(12,'Goan',  'a'),
(13,'Gohn',  'a'),
(14,'Gonnie','a'),
(15,'Gonny', 'a');

-- this is the required order of the results
DECLARE @myOrderString VARCHAR(32) = '456B213A789';

---

A full solution:

I'll provide here my full solution using a loop at the answers section, without taking credit for it as THE answer.

Answer 1

As per the requirements, Below mentioned is the solution...

Select col1, Col2 + ' (' + Convert(Varchar, col1) + ')'
From #T
Where CHARINDEX(CAST(Col1 AS NVARCHAR(MAX)), '3,1,4') <> 0
order by CHARINDEX(CAST(Col1 AS NVARCHAR(MAX)), '3,1,4')

Edit -2 The non numeric values are being ignored in the UDF.

EDIT - 1 Supports the values greater then 9

declare @myTable table(col1 int, col2 varchar(10), col3 varchar(1))

insert @myTable values
(1,'Jon',   'a'),
(2,'Joan',  'b'),
(322,'John',  'a'),
(4,'Jonnie','b'),
(5,'Jonny', 'a')

Select col1, Col2 + ' (' + Convert(Varchar, col1) + ')'
From @myTable T
INNER JOIN 
(
    Select * From dbo.Split('322,1,4', ',')
)K
ON K.val = T.col1
Order by K.id

---

User Defined Function

CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))       
returns @temptable TABLE (id int IDENTITY(1,1), Val Int)       
as       
begin       
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(isnumeric(@slice) = 0) 
        Set @slice = '';

        if(len(@slice)>0)  
            insert into @temptable(Val) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return       
end  

Answer 2

Making your string comma-separated is going to be much better. You can use a split function like this:

CREATE FUNCTION dbo.SplitInts
(
    @List       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN (SELECT Number = ROW_NUMBER() OVER (ORDER BY Number),
        Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number, 
        CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
      FROM sys.all_objects) AS n(Number)
      WHERE Number <= CONVERT(INT, LEN(@List))
      AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
    ) AS y
    WHERE ISNUMERIC(Item) = 1
  );
GO

Now you can do this:

DECLARE @myOrderString VARCHAR(32) = '2,1,3';

DECLARE @t TABLE(col1 INT, col2 VARCHAR(32), col3 CHAR(1));

INSERT @t VALUES
(1,'Jon',   'a'),
(2,'Joan',  'b'),
(3,'John',  'a'),
(4,'Jonnie','b'),
(5,'Jonny', 'a');

SELECT 
  [Row] = s.Number, 
  Outcome = t.col2 + ' (' + CONVERT(VARCHAR(12), t.col1) + ')' 
FROM 
(
  SELECT col1, col2, rn = ROW_NUMBER() OVER (ORDER BY col1)
  FROM @t WHERE col3 = 'a'
) AS t 
INNER JOIN 
  dbo.SplitInts(@myOrderString, ',') AS s
  ON s.Item = t.rn 
ORDER BY s.Number;

Again, results:

Row  Outcome
--   ----------
1    John (3)
2    Jon (1)
3    Jonny (5)

EDIT

Here is a version that doesn't use a function (though I'm not sure why that is so objectionable in this case), doesn't require the list to become comma-separated (a new "hard" requirement, according to the OP's self-answer), and doesn't have to manually populate a table variable in a loop before proceeding (which leads to a less expensive overall plan, even if the final query in the OP's answer does look cheaper when you ignore the loop + inserts). This returns the same results as the OP's self-answer (given the new "A long example" sample data added to the question) but is likewise limited to 15 maximum sort values.

;WITH n(n,c) AS 
(
  SELECT CASE WHEN n < 10 THEN n ELSE n -7 END, CHAR(n+48)
  FROM 
  (
    SELECT TOP (21) n = ROW_NUMBER() OVER (ORDER BY [object_id])
    FROM sys.objects ORDER BY [object_id]
  ) AS x WHERE n BETWEEN 1 AND 9 OR n BETWEEN 17 AND 21
),
x(Outcome, n) AS 
(
  SELECT col2 + ' (' + CONVERT(VARCHAR(12), col1) + ')', 
  ROW_NUMBER() OVER (ORDER BY col1)
  FROM (SELECT col1, col2 FROM @t WHERE col3 = 'a') AS y
)
SELECT [Row] = ROW_NUMBER() OVER (ORDER BY 
    COALESCE(NULLIF(CHARINDEX(n.c, @myOrderString), 0), 16)), 
  Outcome FROM x LEFT OUTER JOIN n ON x.n = n.n
ORDER BY [Row], Outcome;

Answer 3

I'm the writer of the question. I do not take credit for this answer as THE answer.

This solution is based on looping on each of the @myOrderString charachters.

This solution is given here as for inspiration, hoping for a solution based on the solution I described in the question.

Creating the original table and filling it:

DECLARE @t TABLE(Col1 INT, Col2 VARCHAR(32), Col3 CHAR(1));

INSERT @t VALUES
(1,'Jon',   'a'),
(2,'Joan',  'b'),
(3,'John',  'a'),
(4,'Jonnie','b'),
(5,'Jonny', 'a'),
(6,'Don',   'a'),
(7,'Doan',  'b'),
(8,'Dohn',  'a'),
(9,'Donnie','b'),
(10,'Donny', 'a'),
(11,'Gon',   'a'),
(12,'Goan',  'a'),
(13,'Gohn',  'a'),
(14,'Gonnie','a'),
(15,'Gonny', 'a')

This is the required order of the results

DECLARE @myOrderString VARCHAR(32) = '456B213A789'

Converting the string into a table with an Order Colomn (OC)

I wish there were a built-in function to convert a [delimited] string into a table that does something like this.

DECLARE @ot TABLE (PK INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, OC INT)
DECLARE @i int
SET @i = 0
WHILE @i < LEN(@myOrderString) 
    BEGIN
        SET @i = @i + 1
        IF ASCII(UPPER(SUBSTRING(@myOrderString,@i,1))) < 65
            INSERT @ot VALUES (SUBSTRING(@myOrderString,@i,1))
        ELSE
            INSERT @ot VALUES (ASCII(UPPER(SUBSTRING(@myOrderString,@i,1)))-55)
    END

presenting results in the required order:

SELECT 
    ROW_NUMBER() OVER (ORDER BY PK) AS [Row], 
    r.Outcome 
FROM @ot 
INNER JOIN (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY Col1) AS [RRow], 
        Col2 +' (' + CONVERT(varchar(11), Col1) + ')' AS Outcome
    FROM @t 
    WHERE Col3 = 'a') AS r
ON OC=RRow

Appendix

For an interesting result (selecting rows of the result - not required) try to set @myOrderString = '333222111'

Answer 4

Your output table isn't possible given the data supplied because Jonnie is a 'b' not an 'a'. Also, in the example below, 'Jonny' is an 'a' but is not returned because 5 is not in the supplied sort string. Some guidance is needed on how to handle rows like that.

declare @sortTable table (sortId int identity, sortOrder varchar(1))
declare @sortString nvarchar(100) = '314'

declare @position int = 1
declare @sortOrder varchar(1) = substring(@sortString,@position,1)
while @sortOrder != ''
begin
    insert @sortTable (sortOrder) values (@sortOrder)
    set @position = @position + 1
    set @sortOrder = SUBSTRING(@sortString,@position,1)
end


declare @myTable table(col1 varchar(1), col2 varchar(10), col3 varchar(1))

insert @myTable values
(1,'Jon',   'a'),
(2,'Joan',  'b'),
(3,'John',  'a'),
(4,'Jonnie','b'),
(5,'Jonny', 'a')

SELECT ROW_NUMBER() OVER (ORDER BY rsSort.SortID) AS Row, ( Col2 + '(' + Col1 + ')' ) AS Outcome
FROM @myTable rsMain
inner join @sortTable rsSort on rsSort.sortOrder = rsMain.col1
WHERE Col3 = 'a'

Working Example

Answer 5

If you make you're string commasepearated, you an insert it into a temp table and join on that table, the temp table can then contain a sequence number that is incremented each time you insert one of youre integers from the commaseperated string, and that is what you order by

And here is a very fast example (there is propably room for optimization)

DECLARE @string varchar(max),
    @delimiter char(1),
    @xml xml

SELECT @string = '3,1,4',
    @delimiter= ','

SELECT @xml = CONVERT(xml,'<root><s>' + REPLACE(@string,@delimiter,'</s><s>') + '</s></root>')

create table #values
(
    seq integer identity(1, 1),
    value integer
)

insert into #values (value)
SELECT [Value] = T.c.value('.','varchar(20)')
FROM @xml.nodes('/root/s') T(c)

select
    v.seq,
    m.Col2,
    m.Col1
from dbo.myTable m
inner join #values v on m.Col1 = v.value
order by v.seq

drop table #values