PostgreSQL Search Matching Queries?

e2interactive

I normally use mySQL but I am working on a PostgreSQL db and have found that things I normally do in mySQL don't work the same for queries in PostgreSQL. Anyway being new to building queries in PostgreSQL I need some help.

I am trying to build a simply query that'll try and find a match or anything that contains what a user my type. For example:

Say the user types in the search box: Bob

Say I have in my database:

Bob Jones
John Bob Jones
Bobo Jones
Bobolio Jones
Mike Jones
Steve Jones
Steve Bob Jones

I want it to find anything that is simular or contains BOB upper case, lower case whatever. So it would returen only these records because they contain bob .

Bob Jones
John Bob Jones
Bobo Jones
Bobolio Jones
Steve Bob Jones

I am looking for a query that I normally would write like this for a mySQL DB but for a PostgreSQL DB:

$query="SELECT name FROM table WHERE name REGEXP $search_query";

Sxooter

Originally posted by e2interactive

I want it to find anything that is simular or contains BOB upper case, lower case whatever. So it would returen only these records because they contain bob .

Bob Jones
John Bob Jones
Bobo Jones
Bobolio Jones
Steve Bob Jones

I am looking for a query that I normally would write like this for a mySQL DB but for a PostgreSQL DB:
$query="SELECT name FROM table WHERE name REGEXP $search_query"; 
[/B]

There are a few different ways, and it would behoove you to peruse the postgresql manual on this subject.

The relevant sections would be:

strings

pattern matching

For a very simple query, you can use use something like this:

select * from table where upper(namefield) like '%BOB%';

But if you want to use regular expressions, you can do that too. They're covered in the pattern matching section of the docs. But don't expect PostgreSQL to do things the way MySQL does, or vice versa, especially as you move further away from fairly low level SQL92 type syntax.