sql - 寻找标量函数以查找字符串中字符的最后一次出现

Question

表FOO的列FILEPATH类型为VARCHAR(512)。它的条目是绝对路径:

FILEPATH
------------------------------------------------------------
file://very/long/file/path/with/many/slashes/in/it/foo.xml
file://even/longer/file/path/with/more/slashes/in/it/baz.xml
file://something/completely/different/foo.xml
file://short/path/foobar.xml

该表中有约5万条记录，我想知道所有不同的文件名，而不是文件路径:

foo.xml
baz.xml
foobar.xml

这看起来很简单，但是我找不到一个DB2标量函数来让我搜索字符串中字符的最后一次出现。我在监督什么吗？

我可以使用递归查询来做到这一点，但是对于这样一个简单的任务来说，这似乎是过高的，并且(很奇怪)非常慢:

WITH PATHFRAGMENTS (POS, PATHFRAGMENT) AS (
  SELECT
    1,
    FILEPATH
  FROM FOO

UNION ALL

  SELECT
    POSITION('/', PATHFRAGMENT, OCTETS) AS POS, 
    SUBSTR(PATHFRAGMENT, POSITION('/', PATHFRAGMENT, OCTETS)+1) AS PATHFRAGMENT
  FROM PATHFRAGMENTS
)
SELECT DISTINCT PATHFRAGMENT FROM PATHFRAGMENTS WHERE POS = 0

Answer 1

我认为您正在寻找的是 LOCATE_IN_STRING() scalar function。如果您使用负起始值，这就是信息中心所说的:

If the value of the integer is less than zero, the search begins at LENGTH(source-string) + start + 1 and continues for each position to the beginning of the string.

将其与 LENGTH() 和 RIGHT() 标量函数结合使用，您将获得所需的内容:

SELECT
    RIGHT(
         FILEPATH
        ,LENGTH(FILEPATH) - LOCATE_IN_STRING(FILEPATH,'/',-1)
    )
FROM FOO