CODEWITHSUNDEEP
postgresql
harsha kumar Reddy
harsha kumar Reddy

Reputation: 1396

Regular expression search in table in PostgreSql

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

Upvotes: 1

Views: 3957

Answers (1)

Gab
Gab

Reputation: 3520

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

Upvotes: 2

Related Questions