SQL Server CHARINDEX函数

Question

我正在寻找一些函数，用for循环或不同的方式替换列中的所有行。该函数需要通过revers从特定字符(从结束处反斜杠)给出子字符串。

以前：'חוזה - דף ראשון#Y:\Access\Shiduhim\Agreem1\999999.Bmp'

需要在方法之后：'Shiduhim\Agreem1\999999.Bmp'

Answer 1

您的问题可以通过递归CTE来解决(需要了解maxrecursion)：

declare @t table(
  path nvarchar(260)
);

insert into @t(path) values
  (N'חוזה - דף ראשון#Y:\Access\Shiduhim\Agreem1\999999.Bmp'),
  ('C:\abc\defg\hi.png'),
  ('17042.jpg'),
  ('D:\foo\bar.tiff');

declare @n int = 3;

with
  r as (
    select r, 0 as p, 0 as n, iif(charindex('\', r) > 0, 1, 0) as x
    from @t
    cross apply (select reverse(path)) as a(r)
    union all
    select r, a.p, n + 1, iif(n < @n - 1 and charindex('\', r, a.p + 1) > 0, 1, 0)
    from r
    cross apply (select charindex('\', r, p + 1)) as a(p)
    where x = 1
  )
select
  reverse(iif(n < @n, r, left(r, p - 1))) as path
from r
where x = 0;

输出：

+-----------------------------+
|            path             |
+-----------------------------+
| 17042.jpg                   |
| D:\foo\bar.tiff             |
| abc\defg\hi.png             |
| Shiduhim\Agreem1\999999.Bmp |
+-----------------------------+

演示。