Thread: ...
Hi- I'm a newbie at postgresql and created a relational database with perl. What my question is, how do you do a string search in postgresql? I know you can search for string comparisons in oracle but was wondering if this is possible in postgresql? So far I've been using the SELECT syntax for finding words in the database, but this is assuming that the word is exactly the same. If anyone could advise me on this minor problem, I would greatly appreciate it. Peter Landis __________________________________________________ Do You Yahoo!? Talk to your friends online with Yahoo! Messenger. http://messenger.yahoo.com
Try the "like" operator E.g. SELECT * from TABLE where FIELD like '%string%'; Don't forget the % signs - they are the wild card. Peter Landis wrote: > > Hi- > > I'm a newbie at postgresql and created a relational > database with perl. What my question is, how do you > do a string search in postgresql? I know you can > search for string comparisons in oracle but was > wondering if this is possible in postgresql? So far > I've been using the SELECT syntax for finding words in > the database, but this is assuming that the word is > exactly the same. If anyone could advise me on this > minor problem, I would greatly appreciate it. > > Peter Landis > __________________________________________________ > Do You Yahoo!? > Talk to your friends online with Yahoo! Messenger. > http://messenger.yahoo.com > > ************ -- ------------------------------------------------------------ Thomas Reinke Tel: (905) 331-2260 Director of Technology Fax: (905) 331-2504 E-Soft Inc. http://www.e-softinc.com
Not long ago, Peter Landis proclaimed... > I'm a newbie at postgresql and created a relational > database with perl. What my question is, how do you > do a string search in postgresql? I know you can > search for string comparisons in oracle but was > wondering if this is possible in postgresql? So far > I've been using the SELECT syntax for finding words in > the database, but this is assuming that the word is > exactly the same. If anyone could advise me on this > minor problem, I would greatly appreciate it. Very possible. There are several string operators in PostgreSQL. One of my favorite is ~* which does a case-insensitive regular expression search. SELECT * FROM TABLE1 WHERE FIELD1 ~* 'dog'; Hope that helps. -=Fozz -- Doran L. Barton <fozz@iodynamics.com> Iodynamics LLC -- "Internetworking the masses" <URL:http://www.iodynamics.com/>
Peter, Select is the good way, but use LIKE or ~ instead of = for your comparison. It enables you to search on substrings of all kind. SELECT * from my_table where text_field1 like '.....' or ...text_field1 ~ '.....' This operator has extensive features. You should consult the manual. Some examples : .... ~ '^D' begins with D .... ~ 'D' contains D .... ~ '^.D' has D in second place .... ~* 'D' contains D or d And so on. Really too much to mention. Hope this helps Walter De Moudt Peter Landis wrote: > > Hi- > > I'm a newbie at postgresql and created a relational > database with perl. What my question is, how do you > do a string search in postgresql? I know you can > search for string comparisons in oracle but was > wondering if this is possible in postgresql? So far > I've been using the SELECT syntax for finding words in > the database, but this is assuming that the word is > exactly the same. If anyone could advise me on this > minor problem, I would greatly appreciate it. > > Peter Landis > __________________________________________________ > Do You Yahoo!? > Talk to your friends online with Yahoo! Messenger. > http://messenger.yahoo.com > > ************