grep-like query for postgres

Question

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.

Ihor Romanchenko · Accepted Answer · 2014-04-04 08:29:30Z

3

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.

answered Apr 3, 2014 at 20:15

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lajos Veres · Accepted Answer · 2014-04-04 07:52:31Z

2

I think you need the regexp_split_to_table function:

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;

answered Apr 3, 2014 at 19:41

Lajos Veres

13.7k7 gold badges47 silver badges57 bronze badges