如何从含单/双斜杠的数据库表列中提取文件名？

数据库表某列存储的路径数据格式如下，部分值以单斜杠/开头，部分以双斜杠//开头：

/etc/data/env/source/sourcename1/filename1/file-arrivaltimestamp/file-processedtimestamp/file-archivedtimestamp
//etc/data/env/source/sourcename2/filename2/file-arrivaltimestamp/file-processedtimestamp/file-archivedtimestamp
//etc/data/env/source/sourcename3/filename3/file-arrivaltimestamp/file-processedtimestamp/file-archivedtimestamp
/etc/data/env/source/sourcename4/filename4/file-arrivaltimestamp/file-processedtimestamp/file-archivedtimestamp
/etc/data/env/source/sourcename5/filename5/file-arrivaltimestamp/file-processedtimestamp/file-archivedtimestamp

需要提取其中的文件名，尝试使用charindex()、left()函数未得到预期结果，预期输出如下：

filename1
filename2
filename3
filename4
filename5

针对这种固定结构的路径，可先统一格式，再通过字符串拆分或定位截取提取目标内容，以下是两种可行的SQL实现方法：

方法1：使用STRING_SPLIT（适用于SQL Server 2016及以上版本） #

先将开头的双斜杠替换为单斜杠统一路径格式，再按斜杠拆分字符串，取第6个拆分元素即可：

SELECT 
    value AS filename
FROM your_table
CROSS APPLY STRING_SPLIT(REPLACE(your_column, '//', '/'), '/')
WHERE ordinal = 6;

方法2：多次嵌套CHARINDEX定位截取 #

若无法使用STRING_SPLIT，可通过多次调用CHARINDEX定位第5个和第6个斜杠的位置，截取中间内容：

SELECT 
    SUBSTRING(
        normalized_path,
        fifth_slash_pos + 1,
        sixth_slash_pos - fifth_slash_pos - 1
    ) AS filename
FROM (
    SELECT 
        REPLACE(your_column, '//', '/') AS normalized_path,
        CHARINDEX('/', REPLACE(your_column, '//', '/'), CHARINDEX('/', REPLACE(your_column, '//', '/'), CHARINDEX('/', REPLACE(your_column, '//', '/'), CHARINDEX('/', REPLACE(your_column, '//', '/'), CHARINDEX('/', REPLACE(your_column, '//', '/'), 2) + 1) + 1) + 1) AS fifth_slash_pos,
        CHARINDEX('/', REPLACE(your_column, '//', '/'), CHARINDEX('/', REPLACE(your_column, '//', '/'), CHARINDEX('/', REPLACE(your_column, '//', '/'), CHARINDEX('/', REPLACE(your_column, '//', '/'), CHARINDEX('/', REPLACE(your_column, '//', '/'), 2) + 1) + 1) + 1) + 1 AS sixth_slash_pos
    FROM your_table
) AS temp;

说明 #

• REPLACE(your_column, '//', '/')：将开头的双斜杠替换为单斜杠，避免因开头斜杠数量不同导致定位偏差。
• 两种方法均利用路径的固定结构：/etc/data/env/source/[sourcename]/[filename]/...，因此filename始终是拆分后的第6个元素（或第5个斜杠与第6个斜杠之间的内容）。

内容的提问来源于stack exchange，提问作者karen