To do this you will first need to parse your delimited string. The easiest way to parse a delimited string like is to use DelimitedSplit8K. You can find out about this splitter by following the article in my signature about splitting strings.

In this case we need to split the string and keep them in order. Here is where we can really harness the power of the DelimitedSplit8K function. The following code should work for you.

DECLARE @test1 TABLE (letter varchar(1))

INSERT INTO @test1(letter) VALUES('a')

INSERT INTO @test1(letter) VALUES('b')

INSERT INTO @test1(letter) VALUES('c')

INSERT INTO @test1(letter) VALUES('d')

INSERT INTO @test1(letter) VALUES('e')

declare @SortString varchar(10) = 'd;c;b;a;e'

select *

from @test1 t

join

(

select ItemNumber, Item

from dbo.DelimitedSplit8K(@SortString, ';')

) x on t.letter = x.Item

order by x.ItemNumber

Make sure you read that article and understand what it is doing.