MYSQL Help comparing 2 tables

[slasher]

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

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

 

[whitecrook]

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?

 

[suarve]

HAve just been looking on mysql site for something else and came across the STRCOMP function - maybe useful if you're using mysql - see http://dev.mysql.com/doc/refman/5.1/en/string-comparison-functions.html#function_strcmp

 

[slasher]

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.

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

 

[whitecrook]

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....

 

[slasher]

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

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