simple query question

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

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