Comparing two varchar fields from separate tables

[srathi]

hi,

how do i compare two varchar fields?

my table has two fields, name and description. i need to find those records where the name does not match with or exist in the description field anywhere. should i use a "not exists"?

thanks,
s

[pbmods]

Heya, S.

I'm not sure I understand what you are trying to accomplish. Do you need to check to see if one of the fields occurs somewhere in the second field?

P.S., Changed thread title to better describe the problem (did you know that threads whose titles contain three words or less actually get FEWER responses?).

[srathi]

thanks pbmods.

so i have this table, description_tbl that has the fields: name and description. I'd like to compare the two fields and see if the 'value' in the name field exists anywhere in the description field.

so if the name is Microsoft and the desc field has something like "Inc. provides...." it should a part of my results because microsoft does not exist in the description field.

both the name and description fields are varchar.

hope this makes sense.

p.s. how do i edit the title thread?

Heya, S.

I'm not sure I understand what you are trying to accomplish. Do you need to check to see if one of the fields occurs somewhere in the second field?

P.S., Changed thread title to better describe the problem (did you know that threads whose titles contain three words or less actually get FEWER responses?).

[pbmods]

Heya, S.

To answer your second question first, you can't. The moderators on this site are well-versed in what makes a good thread title, and if we feel that a thread title needs editing, we'll just take care of it. However, we prefer it when thread titles do not require editing in the first place.

I encourage you to look over our Posting Guidelines, especially the part about Thread Titles. That'll put you ahead of roughly 85% of all posters with under 100 posts on this site :)

Ok then. Now onto your problem.

Normally, this would be a simple solution; simply use a LIKE subclause in the WHERE clause:

SELECT
        *
    FROM
        `description_tbl`
    WHERE
        `description` LIKE '%Microsoft%';
 

The tricky part comes in because we don't know the exact text that we are searching for (depends on the value of the other field).

Fortunately, MySQL has a function that handles this situation very nicely:

SELECT
        *
    FROM
        `description_tbl`
    WHERE
        `description` LIKE CONCAT('%', `name`, '%');
 

Word of warning: Because MySQL has to calculate the value of CONCAT(), this search is not fully indexable! Be prepared for slow query times on large data sets!

[srathi]

hey pbmods,

i'll def keep the post guidelines in mind going forward. and thanks for looking into my query...

now, there's a slight problem witht concat. the sql version i'm using does not support this function.

also is there a way i can remove characters and spaces from the fields i'm comparing? basically, i'd like to see if "ABB" (probably use the Len function to pick a part of the "name field" ) exists anywhere in the "Description" field because ABB can occur anywhere in the description field.

example:
name: ABB Ltd.
description: ABB, Ltd. provides power and...
(if i can remove all spaces and characters both the fields, then ABBLtd can be compared with the descriptions ABBLtd.

Thanks again for checking this out...
S

[pbmods]

Heya, S.

If your MySQL version doesn't support CONCAT(), then this probably won't be very helpful, either, but...

You might want to look into fulltext searching.

If neither CONCAT() nor fulltext are an option, you may need to load the entire table into another script, such as PHP, and then check for matches that way. Note that if you do this, you might want to cache the results in the table so that you don't have to do this every time the User loads the page!

[srathi]

thanks! i'll try that.

Heya, S.

If your MySQL version doesn't support CONCAT(), then this probably won't be very helpful, either, but...

You might want to look into fulltext searching.

If neither CONCAT() nor fulltext are an option, you may need to load the entire table into another script, such as PHP, and then check for matches that way. Note that if you do this, you might want to cache the results in the table so that you don't have to do this every time the User loads the page!