SQL Reverse函数

Sql sever里面有个自带的reverse函数，这个函数的主要功能是把一个字符产反转。比如对于：

?

1	select REVERSE('hello,world')

?

1	将得到如下的输出：dlrow,olleh

?

1	现在我的问题是，不使用这个函数而使一个字符串反转。

?

1	我找出来一种算法，第一种是使用递归，先找出最后一个字符，然后递归。最好的方式就是使用cte了。代码如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

;with cte1(seq, vv) as(         select 1, 'fine' union all         select 2, 'great' union all         select 3, 'adfinioqweiweio' union all         select 5, 'hello,world' union all         select 4, 'piasdf'),   cte2(seq, vv, vvs, len) as(         select seq, vv, cast(substring(vv, len(vv), 1) as varchar(200)), len(vv)         from cte1 )   ,cte3(seq, vv, vvs, len) as(         select *         from cte2           union all           select c2.seq, c3.vv, cast(c3.vvs + substring(c3.vv, c3.len-1, 1) as varchar(200)), c3.len-1         from cte2 c2 join cte3 c3 on         c2.seq = c3.seq and c3.len <= c2.len and c3.len > 1 )   select * from cte3 where len = 1 order by seq, len

?

1	另外一种方式是，我先得到所有的单个字符，然后把这些字符从后往前聚合起来。代码如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

;with cte1(id, data) as (         select 1, 'Jacob' union all         select 2, 'Sebastn' union all         select 3, 'Hello,world' )   ,Numbers AS (     SELECT *, SUBSTRING(data, n, 1) as sub     FROM cte1 join dbo.Number     on N <= len(data) )   select * from cte1 c1 cross apply(         select substring(sub, 1, len(sub))         from Numbers nm         where nm.id = c1.id         order by n desc         for xml path('') ) as c(p)

?

1

?

1	关于如何使用这种聚合，我将在后面的文章中详解。