Regular expressions in postgresql

Question

I have a table that contains the following three rows

                     name                     
----------------------------------------------
 user - help center test 4 [120,2010-08-19]
 test - help center test 2 [123,2010-01-19]
 help center test [20109,2010-01-01]
(3 rows)

Now i need to get a exact match of "help center test" thus it should return the 3rd row. I basically need to do this exact match as if [20109,2010-01-01] does not exist in help center test [20109,2010-01-01] thus eliminating the brackets and everything within the brackets. NOte the result within the bracket could be anything.

If the brackets weren't there I would use something like SELECT name FROM clients WHERE lower(name) like '%call center test%' and that would give me the result but now I need to do the same with the above results? Is this possible?

ndtreviv · Accepted Answer · 2011-01-12 12:25:51Z

1

According to: http://www.postgresql.org/docs/8.1/static/functions-matching.html :

SELECT name FROM clients WHERE name ~* 'help center test \\[.*?\\]'

Just to clarify that the ~* is a case insensitive match, so there's no need for the lower() function call on the name. This will match all rows that start "help center test [" then have n characters and finally end with "]" (both without double quotes, of course).

edited Jan 12, 2011 at 12:25

answered Jan 12, 2011 at 12:07

ndtreviv

3,6541 gold badge38 silver badges50 bronze badges

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

2 Comments

Elitmiar Over a year ago

~ is case sensitive, it did not give me correct results without lower but when I added it in it worked perfectly, thanx alot for this answer, much appreciated. Maybe the case sensitive has somehing to do with my old postgres version 8.1.22 . Thx again

ndtreviv Over a year ago

Sorry - my bad. Fixed accordingly - you should actually use ~* for case insensitivity.

Collectives™ on Stack Overflow

Regular expressions in postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related