using like

[Anon]

ive got two fields in 2 seperate tables that have similar information.

im thinking that i might be able to use LIKE

select * from table1, table2 where table1.fieldname LIKE table2.%fieldname2%

Is this the correct syntax?

if not what is?

table1 fieldname contains email addresses in this format [e-mail]
table2 fieldname2 contains email addresses in this format
mailto:[e-mail]?subject= etc etc

thanks for any help

r

[[deleted]]

That's not the correct syntax, LIKE compares columns to strings, not columns to columns.

But what exactly are you trying to achieve?

---

A forum, a FAQ, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

[Anon]

Hi vincent

Im trying to select all records from

table2 that contain the same email addresses found in table1

the formats of which are for example sake as follows

table1

id email

1 [e-mail]

table2

id emailadd

8 mailto:[e-mail]?subject= etc etc

the problem im having is knowing how to identify/compare the email string contained within table 2

if the mailto: and ?subject= etc etc were not there it would be a breeze..but they are 🙁

there are quite a few other tables and records i have.

table 2 also has a count column/field

the only way i can think relating the other tables to table2 is by using the emailadd field which i know to contain strings found in other tables mainly table1.

bad design in the first instance yep, but i never contemplated using it in the way I now plan too.

[Anon]

So i cant pull data from table2 using strings found in table1 then?

[Anon]

Vincent wrote

LIKE compares columns to strings, not columns to columns.

This is not quite correct. According to the ANSI sql specification, operands to the like predicate are character value expressions.

Thus this statement

select * from t1,t2 where t1.c1 like '%' || t2.c2 || '%'

is a valid sql statement.

(|| is the ANSI sql operand for string concatenation.)

[[deleted]]

Good point, if slightly debatable 🙂

by concatenating you create a string, and then LIKE compares to a string again (just one that contains data from a column)

[[deleted]]

well, yes you can (read lars's post) but it's a very tricky thing to do, because LIKE will find matches and near-matches...
In your case, if one of the 'URL' records happens to contain two email adresses, you're in trouble...

be carefull!

[Anon]

thankyou both

that query format worked, in that it executed the query, but returned bizarre results, such as multiple instances of the same record.

I got fed up with it in the end so created another column with email addresses that do not contain mailto: or ?subject= etc etc
now i just relate the table using the newly created email column

bad programming from the outset perhaps, butlearning from my mistakes 🙂