CODEWITHSUNDEEP
regexpostgresql
Jerome WAGNER
Jerome WAGNER

Reputation: 22442

grep-like query for postgres

With the ~ operator, it is simple to find all lines in a table for which a column matches a given regexp pattern :

SELECT description from book where description ~ 'hell?o'
matches lines containing hello or helo

Instead of the description, I would like to SELECT a snippet of text around each occurences of the pattern, so if a line contains

description = "aaaheloaaabbbhellobbbcccheloccc"

I would like 3 lines as output :

"aaaheloaaa"
"bbbhellobbb"
"cccheloccc"

which I call a "grep-like" query because it can show extracts of the column where the match is found.

Upvotes: 0

Views: 8746

Answers (2)

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28641

Try something like:

SELECT
  regexp_matches(description,'(.{0,3})('||'hell?o'||')(.{0,3})','g')
FROM
  book
WHERE description ~ 'hell?o'

Without the WHERE clause you will get null in rows, where were no matches for regexp.

Upvotes: 3

Lajos Veres
Lajos Veres

Reputation: 13725

I think you need the regexp_split_to_table function:

http://www.postgresql.org/docs/current/static/functions-matching.html

And you can use it like this:

SELECT foo FROM regexp_split_to_table('the quick brown fox jumped over the lazy dog', E'\\s+') AS foo;

The return:

  foo   
--------
 the    
 quick  
 brown  
 fox    
 jumped 
 over   
 the    
 lazy   
 dog    
(9 rows)

So in your case this would look like:

select res from book, regexp_split_to_table(book.description, E'...hell?o...') res;

Upvotes: 2

Related Questions