Matching with a regular expression in a string in PostgreSQL

Question

I am trying to do something which I am not sure is possible or not.

So here it is, I have one data set in my PostgreSQL table:

select * from company;

 id |     name         | age | 
----+------------------+-----+-
  2 | Spine_[1-9]_Leaf |  12 |

So basically I am storing a regular expression in name in the database. It means if we pass in the query any value like Spine_1_Leaf ... Spine_9_Leaf, it should return the record:

  2 | Spine_[1-9]_Leaf |  12 |

because name string matches name (regex from the db). Please do let me know how can we make it possible. I am trying things like:

select * from company where name ~ 'Spine_1_Leaf'

Which is not working (and it should not be also).

beny23 · Accepted Answer · 2015-06-04 06:13:43Z

1

You've got the search string and term the wrong way round:

select * from company where 'Spine_1_Leaf' ~ name

is what you're after.

answered Jun 4, 2015 at 6:13

beny23

35.1k6 gold badges85 silver badges86 bronze badges

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

3 Comments

beny23 Over a year ago

The purpose I can see for the context of the question is to store some patterns in the database and then query individual values. So I would expect that the actual query would be select * from company where ? ~ name with ? filled with a user input...

Dromethis Over a year ago

Thanks beny it works I would like to know will it work for complex regex also or you will suggest some other approach

beny23 Over a year ago

According to the Postgres docs, it'll work for any POSIX regexes: see here postgresql.org/docs/9.3/static/…

Collectives™ on Stack Overflow

Matching with a regular expression in a string in PostgreSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related