Postgresql database search with regex

Question

I'm using PostgreSQL database with VB.NET and ODBC (Windows).

I'm searching sentences for whole words by combining SELECT with a regular expression, like this:

"SELECT dtbl_id, name 
   FROM mytable 
  WHERE name ~*'" + "( |^)" + TextBox1.Text + "([^A-z]|$)"

This searches well in some cases but because of syntax errors in text (or other reasons) it sometimes fails. For example, if I have the sentence

BILLY IDOL: WHITE WEDDING

the word "white" will be found. But if I have

CLASH-WHITE RIOT

then "white" will not be found, because there is no space between start of word "white".

The simplest solution would be to temporarily change or replace characters in the sentences :,.\/-= etc to spaces.

Is this possible to do in single SELECT line to be suitable for use with .NET/ODBC? Maybe inside the same regular expression?

If it is, how?

Szymon Lipiński · Accepted Answer · 2012-06-23 14:09:40Z

2

Try this:

SELECT 'CLASH-WHITE RIOT' ~ '[[:<:]]WHITE[[:>:]]';

[[:<:]] and [[:>:]] simply mean beginning and end of a word respectively

more info you can find at: http://www.postgresql.org/docs/9.1/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP

answered Jun 23, 2012 at 14:09

Szymon Lipiński

28.8k17 gold badges81 silver badges82 bronze badges

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

4 Comments

Erwin Brandstetter Over a year ago

Care must be taken with high UNICODE code points (like Japanese letters), because the regular expression engine is not reliable with them. Ongoing discussion by the Postgres developers here.

Wine Too Over a year ago

Thanks Szymon, very interesting. With adding one ~* engine becomes case insensitive what is good! But if I try to search O'CONNOR program crashes (I think at '). Additionaly, may be the unicode problem, if search term contains slavic letters like ANDRIĆ or MEŠA then search don't return any data (but it should cause such data exists). Any further recommendations?

kgrittn Over a year ago

To handle quoting issues, see the quote_literal() function or use whatever parameter substitution techniques are available in your development environment. To remove diacritical signs from characters before matching, see the unaccent extension. postgresql.org/docs/9.1/interactive/unaccent.html

Wine Too Over a year ago

Thanks kgritin for very valuable informations. I get some reseults with tsquery already. Besides, do you know how to treat signs like ".,;:" in data sentences like they are spaces? So I can get two words from "MR.JOHN" = MR and JOHN.

Collectives™ on Stack Overflow

Postgresql database search with regex

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related