[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

mysql comparision

using mysql
is there a way
to measure how similar two varchar columns are
0
rgb192
Asked:
rgb192
  • 7
  • 6
1 Solution
 
johanntagleCommented:
can you elaborate on your comparison criteria?
0
 
rgb192Author Commented:
Text string

Word1 word2 word3
Word2 word2 word2

What is numerical similarly percentage.
Or what are possible mysql functions
0
 
johanntagleCommented:
Okay the only thing built-in to MySQL that you can use is the full-text functions.  Not sure if that will satisfy your requirement but see http://dev.mysql.com/doc/refman/5.1/en/fulltext-natural-language.html.  Basically, assuming column1 is the one with the full-text index, you should be able to do something like:

SELECT  column1, column2, MATCH (column1)
AGAINST (column2 IN NATURAL LANGUAGE MODE) AS score
FROM tablename;
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
rgb192Author Commented:
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NATURAL LANGUAGE MODE) AS score
FROM table' at line 2
0
 
johanntagleCommented:
Please post complete information so that we can help you better.  In this case - can you post your complete SQL?
0
 
rgb192Author Commented:
comparing fields from the same column

field1='hello, how are you'
field2='very different'
field3='hi, how are you'
field4='another string
0
 
johanntagleCommented:
You gave me an error message you encountered when you ran a query - I asked for that complete query, not the data you are running it on.
0
 
rgb192Author Commented:
I ran this query and substituted my table and column name

query:

SELECT  column1, column2, MATCH (column1)
AGAINST (column2 IN NATURAL LANGUAGE MODE) AS score
FROM tablename;
0
 
johanntagleCommented:
ok sorry but it looks like MySQL won't accept a column name as a parameter in AGAINST().  You will have to make a script that reads each row and runs that full-text function for each.  Based on your previous questions I participated in you are on Windows, and I unfortunately cannot help you make a script on that platform.
0
 
rgb192Author Commented:
I am using linux for this
0
 
johanntagleCommented:
Okay try using whatever scripting language that has mysql connection libraries and that you are comfortable with.  I'm currently busy the next few days so you may want to post a new related question on it that includes the scripting language you chose.  But first I think you should manually run the query with a few test data to see if it is giving you useful results.  Something like:

SELECT  column1, 'very different' as column2, MATCH (column1)
AGAINST ('very different' IN NATURAL LANGUAGE MODE) AS score
FROM tablename
where primary_key_column=something;

the where clause is meant to prevent mysql from comparing 'very different' with all rows on the table - just change it to something you can actually use.
0
 
rgb192Author Commented:
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NATURAL LANGUAGE MODE) AS score


what do I put for 'very different'
0
 
rgb192Author Commented:
thanks

I will look into comparison further
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now