Thread: simple query question
Thanks in advance: I want to do a particular type of pattern matching in a string, but am not a regexp guru. Can ya help? I want to find a row in a table that has a column that matches a string like "jack nicholson - one flew over the cuckoo's nest" but the columns I have are: actor movie ------ -------- jack nicholson One flew over the cuckoo's nest What I have been trying is UPDATE blah WHERE actor LIKE "jack nicholson - one flew over the cuckoo's nest" OR movie LIKE "jack nicholson - one flew over the cuckoo's nest" of course that doesn't work. So the column in the table is a substring of the search string. Parsing up the search string seems like the hard way to do it, is there an easy way to see if a column's value is a subset of the query string? <sql idiot mode> Also, if there is a DB-independent way to do this without a specific PostgreSQL operator, that would be ideal. </sql idiot mode> Many thanks, Dan
How about
update blah where upper(actor || ' - ' || movie) = upper('jack
nicholson - one flew over the cookoo's nest')
I changed LIKE to = since it looks like you are really wanting something of
an exact match. Like is usually used in conjuction with the % sign for
pattern matches.
I believe Postgres concatinated with the double || operator but I do do that
often and didn't verify the statement, but it should get you in the
ballpark.
Rob
"Dan Maher" <dan.maher@home.com> wrote in message
news:XbcT7.72507$py4.34479716@news2.nash1.tn.home.com...
> Thanks in advance:
>
> I want to do a particular type of pattern matching in a string, but am not
a
> regexp guru. Can ya help?
>
> I want to find a row in a table that has a column that matches a string
like
>
> "jack nicholson - one flew over the cuckoo's nest"
>
> but the columns I have are:
>
> actor movie
> ------ --------
> jack nicholson One flew over the cuckoo's nest
>
> What I have been trying is
>
> UPDATE blah WHERE actor LIKE
> "jack nicholson - one flew over the cuckoo's nest"
> OR movie LIKE
> "jack nicholson - one flew over the cuckoo's nest"
>
> of course that doesn't work. So the column in the table is a substring of
> the search string. Parsing up the search string seems like the hard way to
> do it, is there an easy way to see if a column's value is a subset of the
> query string?
>
> <sql idiot mode>
> Also, if there is a DB-independent way to do this without a specific
> PostgreSQL operator, that would be ideal.
> </sql idiot mode>
>
> Many thanks,
> Dan
>
>
>
>
> I want to find a row in a table that has a column that matches a string like > > "jack nicholson - one flew over the cuckoo's nest" > > but the columns I have are: > > actor movie > ------ -------- > jack nicholson One flew over the cuckoo's nest You should be able to concatenate both fields together (with the spaces and dash) when doing your search: UPDATE blah WHERE actor||' - '||movie = "jack nicholson - one flew over the cuckoo's nest"; Remember, it's just a comparison operator... it just compares what's on the left side to the right, not just a single column with another value. > <sql idiot mode> > Also, if there is a DB-independent way to do this without a specific > PostgreSQL operator, that would be ideal. > </sql idiot mode> I believe || is SQL standard for concatenation, so you should be fine using that. Greg
The "best" solution depends on the structure of your data, and how many assumptions you can validly make about it. A generalized solution would be: ...WHERE position (test_string in field_name) > 0 which will tell you whether your string is contained within the field contents. If you want a case-insensitive match, use lower() or upper() on the two strings being compared. HTH. --- Gregory Wood <gregw@com-stock.com> wrote: > > I want to find a row in a table that has a column > that matches a string > like > > > > "jack nicholson - one flew over the cuckoo's nest" > > > > but the columns I have are: > > > > actor movie > > ------ -------- > > jack nicholson One flew over the cuckoo's nest > > You should be able to concatenate both fields > together (with the spaces and > dash) when doing your search: > > UPDATE blah WHERE actor||' - '||movie = "jack > nicholson - one flew over the > cuckoo's nest"; > > Remember, it's just a comparison operator... it just > compares what's on the > left side to the right, not just a single column > with another value. > > > <sql idiot mode> > > Also, if there is a DB-independent way to do this > without a specific > > PostgreSQL operator, that would be ideal. > > </sql idiot mode> > > I believe || is SQL standard for concatenation, so > you should be fine using > that. > > Greg > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the > unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) __________________________________________________ Do You Yahoo!? Send your FREE holiday greetings online! http://greetings.yahoo.com
On Mon, 17 Dec 2001, Dan Maher wrote:
> Thanks in advance:
>
I've seen several replies but can't remember if you came up with a
solution so I'll add my $0.02 worth.
UPDATE blah
SET whatever = 'whatever'
WHERE lower(actor) LIKE '%jack nicholson%'
AND lower(movie) LIKE '%one flew over the cuckoo's nest%';
The unescaped single quote will probably give you the fits.
If you know for sure the actor and movie then LIKE is probably not what
you want. A simple equals '=' should work.
> actor movie
> ------ --------
> jack nicholson One flew over the cuckoo's nest
>
> What I have been trying is
>
> UPDATE blah WHERE actor LIKE
> "jack nicholson - one flew over the cuckoo's nest"
> OR movie LIKE
> "jack nicholson - one flew over the cuckoo's nest"
>
Good Computing,
Rod
--
Let Accuracy Triumph Over Victory
Zetetic Institute
"David's Sling"
Marc Stiegler