Regular expression search in table in PostgreSql

Question

I am trying to make regular expression search on a table in PostgreSql . But i am not able to find right syntax to do so using single statement . I don't know if i create index column will i be able to do search based on regular expression or not .could any one help me please

The query is like this

SELECT * 
FROM flight_info 
where to_tsvector('english',event_id || ' ' || type || ' ' || category || ' ' || number || ' '|| make || ' '|| model  ) @@ to_tsquery('Incident');

Here Incident is matched exactly based on text But I need to make search base on regular expression like how we do using LIKE .

Or Using like clause on all the column is the only way ?

Or is there a way to give like clause that applies to many columns in query

Gab · Accepted Answer · 2017-03-14 13:50:45Z

2

You will not be able to mix Regex and full text search. It is not possible to use a Regex within to_tsquery()

You can use regexp_matches() instead:

SELECT regexp_matches('foobarbequebaz', '(bar)(beque)');
 regexp_matches 
----------------
 {bar,beque}
(1 row)

SELECT regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)', 'g');
 regexp_matches 
----------------
 {bar,beque}
 {bazil,barf}
(2 rows)

More info at https://www.postgresql.org/docs/9.6/static/functions-matching.html

Update:

To use a Regex in whe WHERE clause, use the ~ operator:

-- Sample schema
CREATE TABLE sample_table (
    id serial,
    col1 text,
    col2 text,
    col3 text
);
INSERT INTO sample_table (col1, col2, col3) VALUES ('this', 'is', 'foobarbequebazilbarfbonk');
INSERT INTO sample_table (col1, col2, col3) VALUES ('apple foobarbequebazequeb', 'rocky', 'sunny');
INSERT INTO sample_table (col1, col2, col3) VALUES ('not', 'a', 'match');

-- Query
SELECT *
FROM sample_table
WHERE (col1 || col2 || col3) ~ '(bar)(beque)';

->

 id |           col1            | col2  |           col3           
----+---------------------------+-------+--------------------------
  1 | this                      | is    | foobarbequebazilbarfbonk
  2 | apple foobarbequebazequeb | rocky | sunny
  (2 rows)

You can use ~* instead to make it case insensitive. More info: https://www.postgresql.org/docs/current/static/functions-matching.html

edited Mar 14, 2017 at 13:50

answered Mar 14, 2017 at 12:04

Gab

3,5381 gold badge13 silver badges25 bronze badges

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

6 Comments

harsha kumar Reddy Over a year ago

could you please suggest me how i should apply this to columns . I want it applied to multiple columns

Gab Over a year ago

Hi @harshakumarReddy I updated my answer to show how to use it with multiple columns at once

harsha kumar Reddy Over a year ago

can we use regexp_matches shown above in where clause . I am not able to get it work .

harsha kumar Reddy Over a year ago

I need columns selected based on regular expression search

Gab Over a year ago

@harshakumarReddy I updated my answer with an example of the operator ~ that you can use to match Regex from the WHERE clause

|

Collectives™ on Stack Overflow

Regular expression search in table in PostgreSql

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related