sql - 如何从字符串中获取部分字符串

Question

如何提取/和.html之间的数值？

http://www.site.com/prod/bunch-of-seo-text-of-different-length/12345687.html
http://www.site.com/prod/bunch-of-seo-text-of-different-length/12345688.html
http://www.site.com/prod/bunch-of-seo-text-of-different-length/12345688.html

我试过了，但我不知道语法

SUBSTRING(link FROM '%/%' FOR '%.html%') 

Answer 1

问题是:

how can I extract the numerical values between / and .html?

正确的答案是:

SELECT substring(link, '/(\d+)\.html')

\d .. [[:digit:]] 的类简写，相当于 [0-9]
+ .. 前面的一个或多个原子
() ..括号捕获匹配
\. .. 点必须被转义以失去其特殊含义

测试:

WITH x(link) AS (
    VALUES
     ('http://www.site.com/prod/bunch-of-text-of-different-length/12345687.html')
    ,('http://www.site.com/prod/bunch-of-text/12345688.html')
    ,('http://www.site2.com/prod/123/text-of-di456fferent-89-len/12345688.html')
    )
SELECT substring(link, '/(\d+)\.html') FROM x;

regexp_matches() 用于捕获多个匹配项，这不是一个好的选择。