sql - 查找具有不同结尾的子串

Question

我正在尝试查找 URL 列表的子字符串，但由于其中一个 URL 末尾的额外字符(正斜杠)而遇到问题。以下是两个数据示例:

• www.url1.com/test
• www.url1.com/test2/

我想获取 URL 的最后一个字符串——在本例中为 test 和 test2。但是，我当前的 REGEX_SUBSTR 只给我第一个 URL 的子字符串和第二个 URL 的 null，因为第二个 URL 有一个正斜杠。

如何创建查询以使其同时返回 test 和 test2？

我的 REGEXP_SUBSTR 查询示例:

REGEXP_SUBSTR(URL, '/([^/]+)$', 1, 1, 'e', 1) as URL_EXTRACT

Answer 1

使用 PARSE_URL :

Returns a JSON object consisting of all the components (fragment, host, path, port, query, scheme) in a valid input URL/URI.

WITH cte AS (
  SELECT CONCAT('http://', COLUMN1) AS URL
  FROM VALUES   ('www.url1.com/test'), 
                ('www.url1.com/test2/'), 
                ('www.url1.com/test/sub_test/')
)
SELECT URL, 
       PARSE_URL(URL) AS parts,
       PARSE_URL(URL):path::text AS path,
       TRIM(PARSE_URL(URL):path::text, '/') AS path
FROM cte;

输出:

---

编辑:

How would you get just "sub_test" for the last example you have there?

可以使用具有负索引的

SPLIT_PART:

WITH cte AS (
  SELECT CONCAT('http://', COLUMN1) AS URL
  FROM VALUES   ('www.url1.com/test'),
                ('www.url1.com/test2/'), 
                ('www.url1.com/test/sub_test/')
)
SELECT URL, 
       PARSE_URL(URL) AS parts,
       PARSE_URL(URL):path::text AS path,
       SPLIT_PART(TRIM(PARSE_URL(URL):path::text, '/'), '/', -1) AS path3
FROM cte;

输出: