Similar to this question Find string match pattern . But this time I want to do this in PostgreSQL.
I have tried :
Select * from my_table where my_param SIMILAR TO "Delivered to (.*) at (.*)"
But it doesn't work.
1 Answer
You can use regexp_matches to extract part of text:
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;
Output:
╔═══════════════════════════════════╦═══════════╦═════════╗
║ col ║ part1 ║ part2 ║
╠═══════════════════════════════════╬═══════════╬═════════╣
║ Delivered to Mr.Smith at Seattle ║ Mr.Smith ║ Seattle ║
╚═══════════════════════════════════╩═══════════╩═════════╝
If you only want to find rows without extracting use:
SELECT *
FROM tab
WHERE col LIKE 'Delivered to % at %'
LIKEStatement for this purposes usefull explanations here