请问一个sql语句，想了半天没想出来!!

[whyNotHere]

declare @s varchar(100)

set @s = 'abcdefghijklm'

------------------
我想通过一个select语句，得到如下结果，不使用循环实现!

ab:cd:ef:gh:ij:kl:m

也就是说，在@s的两两之间插入一个冒号, 不知道select语句如何写? 多谢!

[lsxaa]

select id = identity(int,2,2) into #t from sysobjects a,sysobjects b

seletc @s=stuff(@s,#t.id,1,':') from #t where #t.id<len(@s)

select @s

[lsxaa]

啊 ? 不对,重写

[lsxaa]

select id = identity(int,1,1) into #t from sysobjects a,sysobjects b

seletc @t=":"+left(@s,2),@s=stuff(@s,1,2,'') from #t where len(@s)>0

select stuff(@t,1,1,'')

[whyNotHere]

楼上的老大，能不能给个调试好的语句给我嘛，有几个错误，改成没有错误以后，又没有得到结果 (NULL)，好事做到底，肯定给分底了!

[lsxaa]

还是没测试 :)

declare @t varchar(1000)
declare @s varchar(1000)
set @t=''
set @s = 'abcdefghijklm'
select id = identity(int,1,1) into #t from sysobjects a,sysobjects b
seletc @t=":"+left(@s,2),@s=stuff(@s,1,2,'') from #t where len(@s)>0
select stuff(@t,1,1,'')

[lsxaa]

declare @t varchar(1000)
declare @s varchar(1000)
set @t=''
set @s = 'abcdefghijklm'
select id = identity(int,1,1) into #t from sysobjects a,sysobjects b
select @t=":"+left(@s,2),@s=stuff(@s,1,2,'') from #t where len(@s)>0
select stuff(@t,1,1,'')

[lsxaa]

理解解题思路是关键

[filebat]

--方法和小李哥的一样.
select id = identity(int,1,2)
into #t from sysobjects a,sysobjects b
declare @str varchar(100)
declare @s varchar(100)
set @s = 'abcdefghijklm' --被处理的字符串
set @str='' --用来存放处理后的结果
select @str=@str+':'+string
from (
select id, substring(@s, id, 2) as string
from #t
where id<=len(@s))t
set @str=substring(@str, 2, len(@str))
print @str

[lsxaa]

修改一下第一个
declare @t varchar(1000)
declare @s varchar(1000)
set @t=''
set @s = 'abcdefghijklm'
select id = identity(int,2,2) into #t from sysobjects a,sysobjects b
select @s=stuff(@s,#t.id+(#t.id mod 2)-1,1,':') from #t where #t.id<len(@s)
select @s

[lsxaa]

修改一下 修改一下第一个
declare @t varchar(1000)
declare @s varchar(1000)
set @t=''
set @s = 'abcdefghijklm'
select id = identity(int,2,2) into #t from sysobjects a,sysobjects b
select @s=stuff(@s,#t.id+(#t.id % 2)-1,1,':') from #t where #t.id<len(@s)
select @s

[vivianfdlpw]

declare @t varchar(1000)
declare @s varchar(1000)
declare @row int
set @t=''
set @s = 'abcdefghijklm'
select @row=floor(len(@s)/2)+1

set rowcount @row
select id = identity(int,1,1) into #t from sysobjects a

select @t=@t+":"+left(@s,2),@s=stuff(@s,1,2,'') from #t
select stuff(@t,1,1,'')

drop table #t