Need some help with a basic LIKE % statement

Hi everybody.

I'm trying to formulate a query for a keyword search engine.

I have the following query:



description is varchar(10000)

This query will return all books with the description containing words such as:

1) closely
2) loser
etc.

This is the behavior I would expect. But now I want to take it to the next level. Basically, I want to find only the literal instances of the word LOSE. Not just all instances of the character string within any string.

I'd like to find only the word LOSE, when it is:
1) Surrounded by whitespace, or
2) surrounded by the any of the following characters: !~@#$%^&*()-+=/?<>"':;,.`\|[]{}_

I've tried using IN or = instead of LIKE, but no luck. I also removed the % wildcard but still no luck. These all come up with no results found.

I've also tried:



and just




Do I need to do some stuff with PHP before sending the query word to the database? I am thinking because this is a long varchar(10000), that's why I can't figure it out.

Hmmm....any ideas?

Thanks,

Ted

Side note: PostgreSQL now supports ILIKE, for case insensivite LIKE queries.

Main answer:

You can use regular expressions in PostgreSQL queries. There is a very neat special bracket expression (fifth from last paragraph) that allows you to specify the null string at the beginning and end of a word.

SELECT title FROM books WHERE description ~* '[[:<:]]lose[[:>:]]';

(~ means regular expression, * means case insensitive)

This bracket case is not completely POSIX-compliant, so you might want to do a more standard regex for portability:

SELECT title FROM books WHERE description ~* '[^a-zA-Z0-9]lose[^a-zA-Z0-9]';

The above bracketed class basically means "anything non alpha-numeric". (^ indicates negation, when used inside the brackets)

Also, for search engine work, you might find this full-text indexing tutorial interesting: http://techdocs.postgresql.org/tech...extindexing.php

rycamor,

Thanks once again for being generous with your time and knowledge. I implemented the portable version of that code and it works out perfectly.


I'm going to check out that text indexing tutorial and see what's going on there. That's hot off the presses, only about 2 months old.

Thanks a million.