declare @var varchar(10)
set @var='3124'
-----拆分字符
with cte as 
(    
    select * from
    (
        select left(@var,1) as number ,SUBSTRING(@var,2,LEN(@var)-1) as v_temp
    )A
    union all
    select LEFT(v_temp,1),SUBSTRING(v_temp,2,LEN(v_temp)-1) from cte
    where LEN(v_temp)>0    
)
select 
    number,
    b.N    
from cte a
--关联查询维护表
left join
(
    select 1 as ID,'A' as N union all
    select 2,'B' union all
    select 3,'C' union all
    select 4,'D' union all
    select 5,'E'     union all
    select 6,'F' union all
    select 7,'G' union all
    select 8,'H' union all
    select 9,'I'
)  b on a.number=b.id 
/*
number N
------ ----
3      C
1      A
2      B
4      D

(4 row(s) affected)
*/

------解决方案--------------------
SQL code

declare @T table (ID int,N varchar(10))
insert into @T
select 1,'A' union all
select 2,'B' union all
select 3,'C' union all
select 4,'D'

declare @s varchar(10) set @s='3124'
select N from @T order by charindex(ltrim(ID),@s)
/*
N
----------
C
A
B
D
*/

------解决方案--------------------
SQL code
if object_id('[T]') is not null drop table [T]
go
create table [T]([ID] int,[N] varchar(1))
insert [T]
select 1,'A' union all
select 2,'B' union all
select 3,'C' union all
select 4,'D'
go

declare @s varchar(50)
set @s='3124'
update t set @s=replace(@s,ltrim(id),N)
select @s as result
/**
result
--------------------------------------------------
CABD

(1 行受影响)
**/