表T
ID N
1 A
2 B
3 C
4 D
怎么写一个SQL函数传入的参数为“3124”不固定的返回的结果是"CABD"
不使用游标怎么处理，使用游标回复不给分，谢谢。
------解决方案--------------------
- SQL code
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 C1 A2 B4 D(4 row(s) affected)*/
------解决方案--------------------
- SQL code
declare @T table (ID int,N varchar(10))insert into @Tselect 1,'A' union allselect 2,'B' union allselect 3,'C' union allselect 4,'D'declare @s varchar(10) set @s='3124'select N from @T order by charindex(ltrim(ID),@s)/*N----------CABD*/
------解决方案--------------------
- SQL code
if object_id('[T]') is not null drop table [T]gocreate table [T]([ID] int,[N] varchar(1))insert [T]select 1,'A' union allselect 2,'B' union allselect 3,'C' union allselect 4,'D'godeclare @s varchar(50)set @s='3124'update t set @s=replace(@s,ltrim(id),N)select @s as result/**result--------------------------------------------------CABD(1 行受影响)**/