MYSQL Help comparing 2 tables

Associate
Joined
18 Oct 2002
Posts
100
Need help comparing titles from 2 databases, looking for a match on the model number in the title, lots of different models

eg closest match
500D 500-D (i.e. 500)
S930 S 930 (i.e. 930)

This query works only if the title is the exact same
Code:
Select 
cameras.title,
cameras.sku,
cameras.price
where
cameras.title like concat('%',cameras_digital.title,'%');

Is there a way to match parts of the title?

Thanks in advance

Regards

Dave
 
Soldato
Joined
22 Aug 2005
Posts
8,840
Location
Clydebank
I'd use regexes.

step one - remove all punctuation etc to leave just letters and numbers.

compare each with each other
extract a substring of e.g. 3 or more numbers from 1 and compare with the other
as above but for text.

Doe that help?
 
Associate
OP
Joined
18 Oct 2002
Posts
100
I'd use regexes.

step one - remove all punctuation etc to leave just letters and numbers.

compare each with each other
extract a substring of e.g. 3 or more numbers from 1 and compare with the other
as above but for text.

Doe that help?

Hi

I looked at the REGEXP function, but could work out how to write the query, not used it before.

Code:
Select 
cameras.title, 
cameras.sku, 
cameras.price
from 
cameras,
cameras_digital
where 
cameras.title REGEXP('%',cameras_digital.title,'%');

returns no matches

I had a look at the examples

http://www.tutorialspoint.com/mysql/mysql-regexps.htm

Are you able to give an example?

regards

Dave
 
Soldato
Joined
22 Aug 2005
Posts
8,840
Location
Clydebank
i haven't done it directly in SQL before, I'd use php or something but a regex might be something like this:

remove all punctuation etc to leave just letters and numbers.

s/[^a-zA-Z0-9]+//g (find everything not alphanumeric one or more times and replace with 'nothing')


extract a substring of e.g. 3 or more numbers
s/([0-9],{3,})/%1

*something* like that. I'd need to go and look it up to be sure and depending where and you are implementing the regex the syntax may be different....
 
Associate
OP
Joined
18 Oct 2002
Posts
100
i haven't done it directly in SQL before, I'd use php or something but a regex might be something like this:

remove all punctuation etc to leave just letters and numbers.

s/[^a-zA-Z0-9]+//g (find everything not alphanumeric one or more times and replace with 'nothing')


extract a substring of e.g. 3 or more numbers
s/([0-9],{3,})/%1

*something* like that. I'd need to go and look it up to be sure and depending where and you are implementing the regex the syntax may be different....

OK have removed all punctuation etc but this query doesn't return any values
Code:
Select 
cameras.title, 
cameras.sku, 
cameras.price
from 
cameras,
cameras_digital
where 
cameras.title REGEXP('%',cameras_digital.title,'%');
 
Top Bottom