sql - PostgreSQL中的字符串模式匹配

类似于这个问题。但这次我想在PostgreSQL中做这个。
我试过：

Select * from my_table where my_param SIMILAR TO "Delivered to (.*) at (.*)"

但没用。

最佳答案

您可以使用regexp_matches提取部分文本：

WITH cte AS
(
  SELECT col, regexp_matches(col, 'Delivered to (.*) at (.*)') AS r
  FROM tab
  -- WHERE col LIKE 'Delivered to % at %'
)
SELECT col, r[1] part1, r[2] part2
FROM cte;

SqlFiddleDemo
输出：

╔═══════════════════════════════════╦═══════════╦═════════╗
║               col                 ║  part1    ║  part2  ║
╠═══════════════════════════════════╬═══════════╬═════════╣
║ Delivered to Mr.Smith at Seattle  ║ Mr.Smith  ║ Seattle ║
╚═══════════════════════════════════╩═══════════╩═════════╝

如果只想查找行而不提取使用：

SELECT *
FROM tab
WHERE col LIKE 'Delivered to % at %'

关于sql - PostgreSQL中的字符串模式匹配，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/34461367/