当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - Get amount of different chars (by position) in varchar

问题描述:

I have a table with a varchar, say A, column and the data, for example abcdefghi.

I want to create a query that when I search for the data in column A it's give me the amount of different chars (by position).

I mean, if I search for aacdefghi it will say 1.

If I search for bcdefghia it will say 9.

the length of the strings will be the same.

Any ideas how to do this?

Thanks

EDIT:

My table look like:

A

----

abcdefghi

网友答案:

I think that a script (PHP, Perl, ...) would fit better here, but if you need a MySQL solution you could start with something like this:

SELECT col, COUNT(*)
FROM
  tablename INNER JOIN n
  ON CHAR_LENGTH(tablename.col) >= n.n
WHERE
  SUBSTRING(tablename.col,n.n,1)!=SUBSTRING('aacdefghi', n.n, 1)
GROUP BY
  col;

(you will need a table n that contains numbers 1..maximum length of the string). Please see fiddle here.

网友答案:

What you are looking for is similar to the Levenshtein distance. You can implement it as stored routine in MySQL, see an example here:

http://kristiannissen.wordpress.com/2010/07/08/mysql-levenshtein/

You can modify this for your own needs. It is going to be very slow though, you shouldn't use it on a large data set.

分享给朋友:
您可能感兴趣的文章:
随机阅读: