#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Posts
    409
    Rep Power
    13

    Question 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:

    PHP Code:

    SELECT title FROM books WHERE UPPER
    (descriptionLIKE '%LOSE%'
    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:

    PHP Code:
    SELECT title FROM books WHERE UPPER(descriptionLIKE '_LOSE_'
    and just

    PHP Code:
    SELECT title FROM books WHERE UPPER(descriptionLIKE 'LOSE'

    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
    Last edited by Ted Striker; June 14th, 2002 at 10:29 PM.
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    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/techd...xtindexing.php
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Posts
    409
    Rep Power
    13

    Thumbs up


    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.

IMN logo majestic logo threadwatch logo seochat tools logo