从postgres中的路径中分离出文件名 [英] split out file name from path in postgres

问题描述

我有一个包含Windows文件路径的字段，如下所示：

I have a field that contains windows file paths, like so:

\\fs1\foo\bar\snafu.txt
c:\this\is\why\i\drink\snafu.txt
\\fs2\bippity\baz.zip
\\fs3\boppity\boo\baz.zip
c:\users\chris\donut.c

我需要做的是查找重复文件名的数量（无论它们位于哪个目录中）。因此，我想找到 snafu.txt和 baz.zip，而不是donut.c。

What I need to do is find then number of duplicated files names (regardless of what directory they are in). So I want to find "snafu.txt" and "baz.zip", but not donut.c.

PostgreSQL（8.4）中是否有一种方法可以找到文件路径的最后一部分？如果可以的话，我可以使用count / group查找有问题的孩子。

Is there a way in PostgreSQL (8.4) to find the last part of a file path? If I can do that, then I can use count/group to find my problem children.

推荐答案

regexp_replace(path, '^.+[/\\]', '')

这将与临时向前匹配某些软件产生的斜线。然后，您只需计算其余文件名，例如

This will match the ocassional forward slashes produced by some software as well. Then you just count the remaining file names like

WITH files AS (
    SELECT regexp_replace(my_path, '^.+[/\\]', '') AS filename
    FROM my_table
)
SELECT filename, count(*) AS count
FROM files
GROUP BY filename
HAVING count(*) >= 2;

这篇关于从postgres中的路径中分离出文件名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！