CODEWITHSUNDEEP
sqlregexpostgresql
Wine Too
Wine Too

Reputation: 4655

Postgresql database search with regex

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?

Upvotes: 1

Views: 1029

Answers (1)

Szymon Lipiński
Szymon Lipiński

Reputation: 28634

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

Upvotes: 2

Related Questions