求一SQL语句！

Question

表A
  F_ID          F_path
   1             D:\Bak\070428\2007-05-06\133.mp3
   2             D:\Bak\070428\2007-05-08\122.mp3
   3             D:\Bak\070428\2007-05-09\155.mp3
怎么样把表A中的F_path改为相对路径？结果为
 F_ID          F_path
   1             2007-05-06\133.mp3
   2             2007-05-08\122.mp3
   3             2007-05-09\155.mp3

Answer 1

declare @t table(F_ID int,F_path varchar(200))
insert @t
select   1,             'D:\Bak\070428\2007-05-06\133.mp3' union all
select   2,             'D:\Bak\070428\2007-05-08\122.mp3' union all
select   3,             'D:\Bak\070428\2007-05-09\155.mp3'select F_ID,F_path = 
reverse(substring(reverse(F_path),1,charindex('\',reverse(F_path),charindex('\',reverse(F_path)) + 1) - 1))
from @t/*结果
F_ID        F_path 
----------- ---------------------
1           2007-05-06\133.mp3
2           2007-05-08\122.mp3
3           2007-05-09\155.mp3
*/

Answer 2

select F_ID,F_path = reverse(substring(reverse(F_path),1,charindex('\',reverse(F_path),charindex('\',reverse(F_path)) +1 ) - 1)) from tbA